How to Combine redundant values into one field usi

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

How to Combine redundant values into one field usi

Author

Topic

shankarvalleru
Starting Member

1 Post

Posted - 2010-12-02 : 12:53:34

Hi,

I have a table like this

ID TERM CLASS
---------------------------
304 20103 EE2
156 20102 CS3
167 20101 MC1
167 20101 SE3

As you can see, for the ID 167, there are two different values 'MC1' & 'SE3' under CLASS column. I want to create a query which coalesces
'MC1' & 'SE3' into one field, I would like the resulting table to be like

ID TERM CLASS
---------------------------
304 20103 EE2
156 20102 CS3
167 20101 MC1,SE3

Any help?

SHankar

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-03 : 11:08:29

Try:

-- Show the expected output
SELECT DISTINCT s1.ID,S1.Term,
STUFF((SELECT ',' + s2.Class FROM MyTable AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS Class
FROM MyTable AS s1
ORDER BY s1.ID

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources