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).
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,'-')
精彩评论