Querry to pull data from multiple rows into 1 row

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 2005 Forums

Transact-SQL (2005)

Querry to pull data from multiple rows into 1 row

Author

Topic

Dmh188
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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 12:25:03

[code]
declare @ table (ItemCode varchar(50), Location varchar(50))
insert into @ (ItemCode, Location) values
('Product1', 'Location1'),
('Product1', 'Location2'),
('Product1', 'Location5'),
('Product234','Location2')

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
[/code]

Dmh188
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources