Please start any new threads on our new site at 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)
 Querry to pull data from multiple rows into 1 row

Author  Topic 

Starting Member

37 Posts

Posted - 2015-03-31 : 11:10:52
HEllo. I am currently stuck on something that ive been wanting to find a way around for a while now. I currently have a table that lists ItemCode and Location. When looking at the table some items have multiple locations and some only have 1. It will show up like this:

Product1 Location1
Product1 Location2
Product1 Location5
Product234 Location2

What i need is for when the query is pulling for example, the result will look something like this:
Proudct1 Location1, Location2, Location3
Product234 location2

Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 12:25:03
declare @ table (ItemCode varchar(50), Location varchar(50))
insert into @ (ItemCode, Location) values
('Product1', 'Location1'),
('Product1', 'Location2'),
('Product1', 'Location5'),

select ItemCode, stuff(Locations, 1,2,'') Locations
from @ t
cross apply
select ', ' +location
from @
where ItemCode = t.ItemCode
order by Location
for xml path('')
) _(Locations)
group by ItemCode, Locations
Go to Top of Page

Starting Member

37 Posts

Posted - 2015-03-31 : 13:34:29
Thank you so much! I can not tell you how many times i have had to manually do this.
Go to Top of Page

- Advertisement -