Generate a Comma-Separated List

Question

Post reply

Generate a Comma-Separated List

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
January 30, 2014 at 12:26 am

#396194

Hi All,
Can anyone help to to get the below expected output ?
Inputs :
CREATE TABLE #SAMPLEDATA(
[subTypeId] [int] NULL,
[subTypeValue] [varchar](500) NULL,
[subparentId] [int] NULL,
[childLevel] [smallint] NULL,
[sortOrder] [int] NULL,
[leafFlag] [bit] NULL,
[aggregatesSubTypeId] [int] NULL,
[sourceIdentifier] [varchar](30) NULL,
[origId] [int] NULL,
[RecId] [int] NULL
) ON [PRIMARY]
INSERT INTO #SAMPLEDATA
([subTypeId]
,[subTypeValue]
,[subparentId]
,[childLevel]
,[sortOrder]
,[leafFlag]
,[aggregatesSubTypeId]
,[sourceIdentifier]
,[origId]
,[RecId])
VALUES
(2054000, 'Precious Metals and Minerals',2050000,4,227,0,2000000,NULL,2054000,1),
(2050000, 'Metals and Mining',2010000,3,170,0,2000000,NULL,2054000,2),
(2010000, 'Materials',2000000,2,50,0,2000000,NULL,2054000,3),
(2000000, 'Materials',NULL,1,49,0,2000000,NULL,2054000,4),
(9621063, 'Renewable Electricity',9551702,4,2879,0,9500000,NULL,9621063,1),
(9551702, 'Independent Power and Renewable Electricity Producers',9510000,3,2877,0,9500000,NULL,9621063,2),
(9510000, 'Utilities',9500000,2,2858,0,9500000,NULL,9621063,3),
(9500000, 'Utilities',null,1,2857,0,9500000,NULL,9621063,4)
GO
select * from #SAMPLEDATA
Output :
subTypeIdsubTypeValuesubparentIdchildLevelsortOrderleafFlagaggregatesSubTypeIdsourceIdentifierorigIdRecId
2054000Precious Metals and Minerals2050000422702000000NULL20540001
2050000Metals and Mining2010000317002000000NULL20540002
2010000Materials200000025002000000NULL20540003
2000000MaterialsNULL14902000000NULL20540004
9621063Renewable Electricity95517024287909500000NULL96210631
9551702Independent Power and Renewable Electricity Producers95100003287709500000NULL96210632
9510000Utilities95000002285809500000NULL96210633
9500000UtilitiesNULL1285709500000NULL96210634
Expected Output :
subTypeIdsubTypeValuesubparentIdchildLevelsortOrderleafFlagaggregatesSubTypeIdsourceIdentifierorigIdRecId camasapavalue
2054000Precious Metals and Minerals2050000422702000000NULL20540001 [2050000,2010000,2000000]
9621063Renewable Electricity95517024287909500000NULL96210631 [9551702,9510000,9500000]
let me know for more details.
Thanks
Bhanu

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 1

Hi Anyone pls help me to get this output.

it is urgent pls.

Thanks

Bhanu

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 2

if you required more inputs i can provide.

pls share your observations..

thanks

bhanu

msmvijayakumar SSC Veteran Points: 288 More actions · Answer 3

Hi check with this

; with CommonSeperated_Child

as

(

select1 as 'Level',subTypeId,subTypeValue,subparentId,childLevel,

sortOrder,leafFlag,aggregatesSubTypeId,sourceIdentifier,origId,RecId,CONVERT(varchar(Max),subparentId) camasapavalue

from#SAMPLEDATA

WheresubparentId is null

Union all

selectLevel+1,b.subTypeId,b.subTypeValue,b.subparentId,b.childLevel,

b.sortOrder,b.leafFlag,b.aggregatesSubTypeId,b.sourceIdentifier,b.origId,b.RecId,case when CONVERT(varchar(Max),a.camasapavalue) is null then CONVERT(varchar(Max),b.subparentId)

else CONVERT(varchar(Max),b.subparentId) + ','+CONVERT(varchar(Max),a.camasapavalue)end

camasapavalue

fromCommonSeperated_Childa

Join#SAMPLEDATAbon a.subTypeId = b.subparentId

)

select *

fromCommonSeperated_Child a

Wherelevel = (Select MAX(childLevel) from #SAMPLEDATA b where a.aggregatesSubTypeId = b.aggregatesSubTypeId)

Thanks

Vijay

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT

s.subTypeId, s.subTypeValue, s.subparentId, s.childLevel, s.sortOrder,

s.leafFlag, s.aggregatesSubTypeId, s.sourceIdentifier, s.origId, s.RecId,

camasapavalue = '[' + x.ConcatenatedString + ']'

FROM #SAMPLEDATA s

CROSS APPLY (

SELECT ConcatenatedString = STUFF(

(SELECT ',' + CAST(subTypeID AS VARCHAR(30))

FROM #SAMPLEDATA si

WHERE si.origid = s.origid

AND si.subTypeID <> si.origid

ORDER BY childLevel DESC

FOR XML PATH(''))

,1,1,'')

) x (ConcatenatedString)

WHERE s.subTypeID = s.origid

The hierarchy is already resolved.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 5

Thanks you very much msmvijayakumar and ChrisM@work.

The given 2 solutions are working fine.

Thanks

Bhanu