group columns to one row

Question

Post reply

group columns to one row

demin99

SSC Eights!

Points: 927
More actions
April 1, 2014 at 11:49 am

#396516

I have a table with the following columns
Num,ID,Pos,Value
74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo
Iam trying to get the result as follows.
Num Id Final Value
74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo
I tried to use Stuff and XMLpath but it is not giving me distinct results.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

demin99 (4/1/2014)
I tried to use Stuff and XMLpath but it is not giving me distinct results.

Have you tried nesting them?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

demin99 SSC Eights! Points: 927 More actions · Answer 2

Iam not clear about the nesting.Can you please give me more details..?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'm grumpy today, but something made me help you more. Usually, on a day like this, I would have asked you to post DDL and sample data in a consumable format. This time I did it for you, but you're expected to do this if you want better and faster answers.

CREATE TABLE #Test(

Numint,

IDint,

Posint,

Value varchar(10))

INSERT #Test VALUES(

74 ,1,2,'beck'),(

74 ,1,2,'greg'),(

74 ,1,9,'mike'),(

74 ,1,9,'laggo'),(

74 ,2,2,'beck'),(

74 ,2,2,'greg'),(

74 ,2,9,'mike'),(

74 ,2,9,'laggo')

SELECT Num,

ID,

FinalValue = STUFF((SELECT ';' + CAST( Pos AS varchar( 5))

+ (SELECT ',' + Value

FROM #Test t3

WHERE t2.Num = t3.Num

AND t2.ID = t3.ID

AND t2.Pos = t3.Pos

FOR XML PATH(''))

FROM #Test t2

WHERE t1.Num = t2.Num

AND t1.ID = t2.ID

GROUP BY Num,

ID,

Pos

FOR XML PATH('')),1,1, '')

FROM #Test t1

GROUP BY Num,

ID

GO

DROP TABLE #Test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

demin99 SSC Eights! Points: 927 More actions · Answer 4

Thanks a lot Luis. the logic is brilliant..It helped me save lot of time.

Sorry for not posting the DDL.Next time I will definetly do that. Thanks again..!!