Transform a list to a table - 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

Transform a list to a table

Author

Topic

tal.shir
Starting Member

2 Posts

Posted - 2008-07-03 : 12:27:44

Hello,
Hope some one can help me with this

I have a table that contains the following fields: Name, date and value.

for example

name date value

aa 1/1 1
aa 2/1 2
aa 3/1 3
bb 1/1 10
bb 2/1 20
bb 3/1 30
cc 1/1 50
cc 2/1 40
cc 3/1 30
cc 4/1 20

I would like to create the table where the fileds names are date, value_aa,value_bb,value_cc- it means:

date value_aa value_bb value_cc

1/1 1 10 50
2/1 2 20 40
3/1 3 30 30
4/1 NaN NaN 20

Hope you have some ideas....
Thanks

contrari4n
Starting Member

27 Posts

Posted - 2008-07-03 : 13:36:03

Assuming you are using 2005, have a look at the PIVOT operator.

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-03 : 14:40:25

Search this site for Row Concatenation. This is from Peso:

DECLARE @Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE
FROM @Sample AS s2
WHERE s2.ID = s1.ID
ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE
FROM @Sample AS s2
WHERE s2.ID = s1.ID
FOR XML PATH('')
), 1, 1, ''
) AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

tal.shir
Starting Member

2 Posts

Posted - 2008-07-04 : 03:34:08

Thanks I'll take a look

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-05 : 04:22:05

create table sample
(
name varchar(20),
date datetime,
value int
)
go

insert into sample
select 'aa','1/1/2001',1
union all
select 'aa','2/1/2001',2
union all
select 'aa','3/1/2001',3
union all
select 'bb','1/1/2001',10
union all
select 'bb','2/1/2001',20
union all
select 'bb','3/1/2001',30
union all
select 'cc','1/1/2001',50
union all
select 'cc','2/1/2001',40
union all
select 'cc','3/1/2001',30
union all
select 'cc','4/1/2001',20
go

SELECT date,
SUM(CASE WHEN name='aa' THEN value ELSE 0 END) AS value_aa,
SUM(CASE WHEN name='bb' THEN value ELSE 0 END) AS value_bb,
SUM(CASE WHEN name='cc' THEN value ELSE 0 END) AS value_cc
FROM sample
GROUP BY date

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources