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.
Author |
Topic |
system243trd
Starting Member
9 Posts |
Posted - 2012-12-20 : 12:31:58
|
I have the following tableShoeID Shoe1 A2 B1 A1 F2 C4 A4 A1 CHow can I create a query that concatenates the shoe (without spaces) to one field for each shoeid e.g.ShoeID Shoe1 AAFC2 BC4 AA |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-20 : 12:37:59
|
[code]SELECT ShoeID,(SELECT '' + Shoe FROM table WHERE ShoeID = t.ShoeID FOR XML PATH(''))FROM (SELECT DISTINCT ShoeID FROM table) t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
system243trd
Starting Member
9 Posts |
Posted - 2013-01-03 : 14:34:57
|
Hi Visakh,Thanks for the reply, please could you explain how this query works? I have tried reading about XML path but cannot figure out how the query worksThanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 02:44:32
|
The subquery using FOR XML PATH generates a xml string with dummy node name which in effect will give you ling list of shoes. You take distinct of ShoeID as a separate derived table with alias as t and then pass these ids to inner query to get long list of shoes associated to each of the id values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|