SQL Server Forums - Query that concatenates multiple rows

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query that concatenates multiple rows

New Topic

Reply to Topic

Printer Friendly

Author

Topic

system243trd
Starting Member

2 Posts

Posted - 12/20/2012 : 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

India
47173 Posts

Posted - 12/20/2012 : 12:37:59


SELECT ShoeID,
(SELECT '' + Shoe FROM table WHERE ShoeID = t.ShoeID FOR XML PATH(''))
FROM (SELECT DISTINCT ShoeID FROM table) t

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

system243trd
Starting Member

2 Posts

Posted - 01/03/2013 : 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

India
47173 Posts

Posted - 01/04/2013 : 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/

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.05 seconds.