Column Data to rows - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Column Data to rows

Author

Topic

buzzi
Starting Member

48 Posts

Posted - 2009-01-30 : 14:41:43

Hello All,
Can somebody please try to help me write a query to get the information that i am looking for.

Below is the sample data

Name Product
john Berries
Phillips sqash
john apples
Phillips apples
Phillips Berries

I need the out put to look like this

Name Products
John Berries,Apples
Phillips sqash,apples,berries

Please let me know how can i get the desired result above.

I know if i can create a function and pass the name as parameter to the function and get the all the products for the user in a comma separated way. Some thing that i can learn if there is a way to do it directly from a query

Thanks for the help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 14:46:56

Here I have given answer in both SQL 2000 and 2005.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118937

buzzi
Starting Member

48 Posts

Posted - 2009-01-30 : 15:39:18

Sodeep thank you so much. Sorry that i gave half information, But your response is still very usefull to me. But is there a way to extend this

Name Product BuyDate
john Berries 1/1/09
Phillips sqash 1/1/09
john apples 1/2/09
Phillips apples 1/1/09
Phillips Berries 1/2/09
john oranges 1/2/09

the result should be like this
name product buydate
john berries 1/1/09
john apples,oranges 1/2/09
Phillips sqash,apples 1/1/09
Phillips berries 1/2/09

Thanks for your help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 16:18:12

[code]--Create Table Variable
Declare @t table
([Name] varchar(15) ,Product varchar(15), BuyDate datetime)

--Prepare Sample Data
Insert @t
Select 'john','Berries', '1/1/09' union all
Select 'Phillips','sqash', '1/1/09' union all
Select 'john','apples', '1/2/09' union all
Select 'Phillips','apples','1/1/09' union all
Select 'Phillips','Berries', '1/2/09' union all
Select 'john','oranges','1/2/09'

Select [Name],
Max(Case when seq =1 then Product else '' end)+ ',' +
Max(Case when seq =2 then Product else '' end)+ ',' +
Max(Case when seq =3 then Product else '' end)+ ',' +
Max(Case when seq =4 then Product else '' end) as Product,
BuyDate
from
(SELECT [Name],Product,BuyDate,Row_number() over(partition by [Name],BuyDate order by [Name])as seq
from @t) Z
Group by [Name],BuyDate

--output
name product BuyDate
john Berries, 2009-01-01 00:00:00.000
john apples,oranges,, 2009-01-02 00:00:00.000
Phillips sqash,apples,, 2009-01-01 00:00:00.000
Phillips Berries,, 2009-01-02 00:00:00.000[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 16:30:58

[code]--Create Table Variable
Declare @t table
([Name] varchar(15) ,Product varchar(15), BuyDate datetime)

--Prepare Sample Data
Insert @t
Select 'john','Berries', '1/1/09' union all
Select 'Phillips','sqash', '1/1/09' union all
Select 'john','apples', '1/2/09' union all
Select 'Phillips','apples','1/1/09' union all
Select 'Phillips','Berries', '1/2/09' union all
Select 'john','oranges','1/2/09'

SELECT z.name,LEFT(sl.list,LEN(sl.list)-1) as product,z.BuyDate
FROM(SELECT DISTINCT [name],BuyDate FROM @t) z
CROSS APPLY(SELECT product + ',' AS [text()]
FROM @t
WHERE [name] = z.name and BuyDate = z.BuyDate
FOR XML PATH(''))sl(list)

--output
name product BuyDate
john Berries 2009-01-01 00:00:00.000
john apples,oranges 2009-01-02 00:00:00.000
Phillips sqash,apples 2009-01-01 00:00:00.000
Phillips Berries 2009-01-02 00:00:00.000[/code]

buzzi
Starting Member

48 Posts

Posted - 2009-01-30 : 17:20:49

Thanks a lot, i modified the query that you gave
this works for me perfectly

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 17:24:43

You are Welcome

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 18:01:14

Or Simply this:

quote:

Originally posted by sodeep

--Create Table Variable
Declare @t table
([Name] varchar(15) ,Product varchar(15), BuyDate datetime)

--Prepare Sample Data
Insert @t
Select 'john','Berries', '1/1/09' union all
Select 'Phillips','sqash', '1/1/09' union all
Select 'john','apples', '1/2/09' union all
Select 'Phillips','apples','1/1/09' union all
Select 'Phillips','Berries', '1/2/09' union all
Select 'john','oranges','1/2/09'

SELECT DISTINCT [name],
STUFF((SELECT ','+ Product 
FROM @t WHERE [name] = m.[name] and BuyDate = m.BuyDate FOR XML PATH('')),1,1,'') AS List,BuyDate
FROM @t m

--output
name        product             BuyDate
john	    Berries	           2009-01-01 00:00:00.000
john	    apples,oranges	   2009-01-02 00:00:00.000
Phillips	sqash,apples       2009-01-01 00:00:00.000
Phillips	Berries	           2009-01-02 00:00:00.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:22:38

[code]
SELECT DISTINCT t.name,STUFF((SELECT ','+ product FROM @t
WHERE [name] = t.name and BuyDate = t.BuyDate FOR XML PATH('')),1,1,''),t.BuyDate
FROM Table t
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-31 : 09:06:51

quote:

Originally posted by visakh16


SELECT DISTINCT t.name,STUFF((SELECT ','+ product FROM @t
WHERE [name] = t.name and BuyDate = t.BuyDate FOR XML PATH('')),1,1,''),t.BuyDate
FROM Table t

I have already shown this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 09:21:38

quote:
Originally posted by sodeep

quote:
Originally posted by visakh16

SELECT DISTINCT t.name,STUFF((SELECT ','+ product FROM @t
WHERE [name] = t.name and BuyDate = t.BuyDate FOR XML PATH('')),1,1,''),t.BuyDate
FROM Table t

I have already shown this.

Oops sorry
seems like i didnt notice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources