开发者

Concatenate SQL rows from CTE/Temp table based on user selected values in a hierarchy

I need help in concatenating multiple rows in SQL to one row, for the key value (EID) and the values selected in the hierarchy tree. The tree looks like this (its on an RSA Archer based platform).

Concatenate SQL rows from CTE/Temp table based on user selected values in a hierarchy

I have a CTE/Temp table (say its : #tmpBH) that looks like the below [It has 2 fields - EID and Fullpath]. This is based on values selected. For eg. if 'a1' is selected then the Eid takes the value from the root (WFC) till 'a1'.

EID Fullpath
E1 WFC - CSBB : a1
E1 WFC - CL
E2 WFC - CSBB : a2
E2 WFC - CL : b2 : c1
E2 WFC - CL : b1
E3 WFC - WIM : c2 :开发者_运维知识库 d2
E3 WFC - WIM : c1

The output that I am looking for is as below. Basically, a concatenation of all EIDs into a single row from the above table.

EID Fullpath
E1 WFC - CSBB : a1 - CL
E2 WFC - CSBB : a2 - CL : b2 : c1 - CL : b1
E3 WFC - WIM : c2 : d2 - WIM : c1


You can look for distinct branches in your tree, a query would be like this:

DROP TABLE IF EXISTS #TEMP
SELECT  'E1' EID,'WFC - CSBB : a1' Fullpath
INTO #TEMP
UNION ALL 
SELECT 'E1','WFC - CL'
UNION ALL 
SELECT 'E2','WFC - CSBB : a2'
UNION ALL 
SELECT 'E2','WFC - CL : b2 : c1'
UNION ALL 
SELECT 'E2','WFC - CL : b1'
UNION ALL 
SELECT 'E3','WFC - WIM : c2 : d2'
UNION ALL 
SELECT 'E3','WFC - WIM : c1'

SELECT DISTINCT  EID,RIGHT(STUFF((
                    SELECT DISTINCT  ' - ' + value
                    FROM #TEMP b
                    CROSS APPLY string_split(Fullpath,'-')
                    WHERE a.EID = b.EID
                    ORDER BY 1 DESC
                    FOR XML PATH('')
                ), 1, 1, '') ,LEN(STUFF((
                    SELECT DISTINCT  ' - ' + value
                    FROM #TEMP b
                    CROSS APPLY string_split(Fullpath,'-')
                    WHERE a.EID = b.EID
                    ORDER BY 1 DESC
                    FOR XML PATH('')
                ), 1, 1, '')) - 2)
FROM #TEMP a
CROSS APPLY string_split(Fullpath,'-')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜