April 26, 2009 at 3:16 am
Hi all,
I have table like this:
ID colour
1 red
1 blue
1 orange
2 red
2 blue
2 orange
3 red
3 blue
I want like this
ID colur
1 red blue orange
2 red blue orange
3 red blue
April 26, 2009 at 9:09 am
There are a couple of ways you can do this. Here is one example.
Declare @Table Table (id int, color varchar(10))
Insert Into @Table
Select 1, 'red' UNION ALL
Select 1, 'blue' UNION ALL
Select 1, 'orange' UNION ALL
Select 2, 'red' UNION ALL
Select 2, 'blue' UNION ALL
Select 2, 'orange' UNION ALL
Select 3, 'red' UNION ALL
Select 3, 'blue'
SELECT
t1.ID,
List = substring((SELECT ( ', ' + color )
FROM @Table t2
WHERE t1.ID = t2.ID
ORDER BY
ID
FOR XML PATH( '' )
), 3, 1000 )FROM @Table t1
GROUP BY ID
April 26, 2009 at 10:25 am
Ken Simmons (4/26/2009)
There are a couple of ways you can do this. Here is one example.
Declare @Table Table (id int, color varchar(10))Insert Into @Table
Select 1, 'red' UNION ALL
Select 1, 'blue' UNION ALL
Select 1, 'orange' UNION ALL
Select 2, 'red' UNION ALL
Select 2, 'blue' UNION ALL
Select 2, 'orange' UNION ALL
Select 3, 'red' UNION ALL
Select 3, 'blue'
SELECT
t1.ID,
List = substring((SELECT ( ', ' + color )
FROM @Table t2
WHERE t1.ID = t2.ID
ORDER BY
ID
FOR XML PATH( '' )
), 3, 1000 )FROM @Table t1
GROUP BY ID
Hi, I tried looking up the MSDN forum for XML PATH construct; I am still struggling to understand what does the (substring [select for xml path] 3, 1000) do in the code above. Kindly help...
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 26, 2009 at 12:49 pm
Easiest answer: Try and you'll see the difference...
Or, to explain it without trying:
The substring function removes the first two characters, which are a comma and a blank as per the "SELECT ( ', ' + color )" statement.
Otherwise the result set would look like ", red, blue, orange" instead of "red, blue, orange".
So, if you decide to separate the values by a blank character instead of comma + blank you'd need to change the SUBSTRING function to start a Position 2 instead of 3.
April 27, 2009 at 12:50 am
Thanks Lutz. I understand the substring part. I was trying to make sense of the 1000 part. Anyway I have got it now. Thanks for taking the time to explain.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 28, 2009 at 5:40 pm
The 1000 part is just an impossibly long number of characters to make up for the fact that we don't know exactly how long the string is. You start at position 3 and get the next 1000 characters, except that 'red' has only 3 and 'orange' has only 6 characters. The SUBSTRING function stops at the end of the string.
Todd Fifield
May 28, 2009 at 9:32 am
I have a similar problem but I don't know what the values are. I want to build a row that has a list of ID's
Here is a sample dataset
ACCOUNT_ID IND_ID
123456 0234569
123456 0321543
123456 0345737
123456 0785785
123456 0934625
When I select the IND_ID where ACCOUNT_ID = 123456 I want the results to be on a single line
0234569, 0321543, 0345737, 0785785, 0934625
All suggestions are gratefully received.
thanks
May 28, 2009 at 9:39 am
Marcus why don't you try the supplied solution and just add a where clause?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 28, 2009 at 9:43 am
🙂 Because it's still early in the day and I haven't drunk enough coffee. 🙂
thanks it worked
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply