Have the following table: MF_MO
SO_NO EST_MO DEP
1001 1 1
1001 1 2
1002 1 1
2, 3, 1002
Another table DEPT

DEP NAME
1
a workshop2 second workshop
3 three workshops


That the query results show the following

SO_NO EST_MO NAME
1001 1 one shop, two workshops
1002
1 a workshopThree workshops 1002 2

How to use the function to realize query results

CodePudding user response:

Which version of the library?
Use string_agg above 2017, the previous version to use for the path syntax,

CodePudding user response:

The 2008 version of the database

CodePudding user response:

 SELECT * INTO MF_MO FROM (
SELECT '1001' SO_NO, 1 EST_MO, 1 DEP UNION ALL 
SELECT '1001' SO_NO, 1 EST_MO, 2 DEP UNION ALL 
SELECT '1002' SO_NO, 1 EST_MO, 1 DEP UNION ALL 
SELECT '1002' SO_NO, 2 EST_MO, 3 DEP) AS A 

SELECT * INTO DEPT FROM (
SELECT 'a shop NAME, 1 DEP UNION ALL 
SELECT 'second workshop' NAME, 2 DEP UNION ALL 
SELECT the 'three workshops' NAME, 3 DEP) AS A 

WITH CTE AS (SELECT DISTINCT SO_NO, EST_MO FROM MF_MO) 
SELECT *, STUFF ((SELECT the ', '+ NAME FROM DEPT A JOIN MF_MO ON a. d. B EP=B.D EP WHERE B.S O_NO=CTE. SO_NO AND B.E ST_MO=CTE. EST_MO FOR XML PATH ('')), 1, 1, ' ') AS the NAME FROM CTE 

CodePudding user response:

The CREATE FUNCTION [dbo] [f_strScSA_ScNo] (@ id int)
RETURNS a varchar (8000)
AS
The BEGIN
DECLARE @ r varchar (8000)
The SET @ r='
SELECT @ r=@ r + ', '+ ScNo
The FROM scSADtl
WHERE MFID=@ id
GROUP BY ScNo
RETURN STUFF (@ r, 1, 1, ' ')
END
GO

Can you write down according to the way?

CodePudding user response:

Select t2 so_no, t2. Est_mo listagg (t2) name) within group (order by t2. So_no, t2 s.e st_mo)
The from (select a. so_no, a.e st_mo, a. d. ep dep dep1, b.d ep dep2, b.n ame from mf_mo a, dep b where a. d. ep=b.d ep) t2
Group by t2 so_no, t2. Est_mo;