根据仓位值集ID取仓位名称

--注意调整红色业务数据表名

IF OBJECT_ID('TEMPDB..#DATA') IS NOT NULL 

BEGIN DROP TABLE #DATA END

SELECT ROW_NUMBER ( ) OVER ( ORDER BY T.FSTOCKID ) FROWNUM,

T.FSTOCKID,

T.FSTOCKLOCID,

'FF'+CAST (T.FFLEXID AS VARCHAR ) FFLEXID,

'' FFLEXVALUE

INTO #DATA

FROM (

SELECT DISTINCT

A.FSTOCKID,

A.FSTOCKLOCID,

B.FFLEXID

FROM

V_PLM_PRIRSSDETAIL A

INNER JOIN T_BD_STOCKFLEXITEM B ON A.FSTOCKID=B.FSTOCKID) T

ALTER TABLE #DATA ALTER COLUMN FFLEXVALUE NVARCHAR(MAX)

DECLARE @I INT = 1

WHILE (@I < (SELECT COUNT(*) FROM #DATA ))

BEGIN

    DECLARE @SQLRUN VARCHAR(MAX)

    SET @SQLRUN = 

( 'UPDATE #DATA 

SET FFLEXVALUE=

(SELECT ' + CAST ((SELECT FFLEXID FROM #DATA WHERE FROWNUM=@I) AS VARCHAR ) + ' FROM T_BAS_FLEXVALUESDETAIL A WHERE A.FID= '+CAST ((SELECT FSTOCKLOCID FROM #DATA WHERE FROWNUM=@I) AS VARCHAR ) +') WHERE FROWNUM='+CAST (@I AS VARCHAR )

) 

    EXEC(@SQLRUN)

    SET @I = @I + 1

END

SELECT T2.FSTOCKLOCID, FSTOCKLOC = ( STUFF(( SELECT ',' + T1.FNAME FROM 

(SELECT DISTINCT A.FSTOCKLOCID,B.FNAME FROM #DATA A

INNER JOIN T_BAS_FLEXVALUESENTRY_L B ON A.FFLEXVALUE=B.FENTRYID AND B.FLOCALEID=2052

) T1 WHERE T1.FSTOCKLOCID = T2.FSTOCKLOCID FOR XML PATH ( '' ) ),1,1,'' ) ) 

FROM

(SELECT DISTINCT A.FSTOCKLOCID,B.FNAME FROM #DATA A

INNER JOIN T_BAS_FLEXVALUESENTRY_L B ON A.FFLEXVALUE=B.FENTRYID AND B.FLOCALEID=2052

) T2

GROUP BY

T2.FSTOCKLOCID