Multiple rows into single row - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Multiple rows into single row

Author

Topic

sneupane
Starting Member

28 Posts

Posted - 2010-02-11 : 15:52:57

Hi everyone

I have two tables

1st table

Customer Number
Z12564 1
Z34597 2
Z45679 3

2nd Table is

Item Customer
a Z12564
b Z12564
b Z34597
b Z45679
c Z45679
c Z12564

I would like a result like this in MS SQL
Hi everyone

I have two tables

1st table

Customer Number
Z12564 1
Z34597 2
Z45679 3

2nd Table is

Item Customer
a 1
b 1,2,3
c 1,3

I tried using coalesce and cast function but it did not work for me. Does anyone know how to solve such issues in MS SQL.

SAR

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 15:56:29

What version of SQL server are you using?

sneupane
Starting Member

28 Posts

Posted - 2010-02-11 : 16:00:21

SQL server 2005

SAROJ

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 16:06:35

Try this

SELECT DISTINCT	s1.Item,
		STUFF((SELECT DISTINCT ',' + convert(varchar(10),s3.Number) FROM table2 AS s2 inner join table1 s3 on s2.Customer = s3.Customer WHERE s2.Item = s1.Item ORDER BY ',' + convert(varchar(10),s3.Number) FOR XML PATH('')), 1, 1, '') AS Customers
FROM		table2 AS s1
ORDER BY	s1.Item

where table1 is your customer table and table2 is your items table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:26:08

or look into 3rd scenario below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

sneupane
Starting Member

28 Posts

Posted - 2010-02-12 : 08:40:59

Thanks Vijay! It worked for me.

SAROJ

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-12 : 09:14:32

Np. You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources