Query that concatenates multiple rows

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

Transact-SQL (2008)

Query that concatenates multiple rows

Author

Topic

system243trd
Starting Member

9 Posts

Posted - 2012-12-20 : 12:31:58

I have the following table

ShoeID Shoe
1 A
2 B
1 A
1 F
2 C
4 A
4 A
1 C

How can I create a query that concatenates the shoe (without spaces) to one field for each shoeid e.g.
ShoeID Shoe
1 AAFC
2 BC
4 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 MVP
http://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 works

Thanks

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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources