ISSUE WITH QUERY - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

ISSUE WITH QUERY

Author

Topic

kbbatt01
Starting Member

7 Posts

Posted - 2010-04-20 : 12:09:35

i have the result set like this

id firstname lname race
1 kishore gupta W
1 kishore gupta B
1 kishore gupta H
2 sachin gupta B
2 sachin gupta A

my query needs to dispaly the result like this

id firstname lname race
1 kishore gupta W,B,H
2 sachin gupta B,A

Any Help will be appriciated
Thanks in advance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-20 : 12:27:28

Try this:

DECLARE @Sample TABLE (ID INT, FirstName VARCHAR(20), lname VARCHAR(20), race Varchar(10))

INSERT @Sample
SELECT 1, 'kishore','gupta','W' UNION ALL
SELECT 1, 'kishore','gupta','B' UNION ALL
SELECT 1, 'kishore','gupta','H' UNION ALL
SELECT 2, 'sachin','gupta','B' UNION ALL
SELECT 2, 'sachin','gupta','A'

-- Show the expected output
SELECT distinct s1.ID, s1.Firstname,S1.lname,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.Race FROM @Sample AS s2 WHERE s2.ID = s1.ID
ORDER BY ',' + s2.Race FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

Regards,
Bohra

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:37:39

do you really need that ORDER BY inside STUFF? I dont think it will give OP's sample result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 02:29:42

Hi Visakh,

You are correct. The order by is not reqd in the query.
I have just reframed the query removing the order by. I think this should give desired result to OP.

Thats the reason i say- > I am here to learn from Masters.

SELECT distinct s1.ID, s1.Firstname,S1.lname,
STUFF((SELECT ',' + s2.Race FROM @Sample AS s2 WHERE s2.ID = s1.ID
FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

Thanks,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 05:47:30

looks fine now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kbbatt01
Starting Member

7 Posts

Posted - 2010-04-22 : 15:17:16

THANKYOU GUYS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources