Not Row to Column But rows+Colyumn to Column

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

Not Row to Column But rows+Colyumn to Column

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-24 : 02:49:13

Hi all. This is my normal query.

select * from table
order by item

I get. For Example the following.

Item Year Month1 Month2 Month3
---------------------------------------------
abc 2005 1 22 4
abc 2006 45 64 4
abc 2007 99 46 99
bbc 2005 54 64 4
bbc 2006 2 4 77
bbc 2007 46 7 98

I want my records to be returned as follow as possible.

Item | 2005 Month1 | 2005 Month2 | 2005 Month3 | 2006 Month1 | 2006 Month2 |
abc | 1 | 22 | 4 | 45 | 64
bbc | 54 | 64 | 4 | 2 | 4

Please Guide me to get this working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 02:54:49

[code]SELECT Item,
SUM(CASE WHEN Year=2005 THEN Month1 ELSE 0 END) AS [2005 Month1],
SUM(CASE WHEN Year=2005 THEN Month2 ELSE 0 END) AS [2005 Month2],
SUM(CASE WHEN Year=2005 THEN Month3 ELSE 0 END) AS [2005 Month3],
SUM(CASE WHEN Year=2006 THEN Month1 ELSE 0 END) AS [2006 Month1],
SUM(CASE WHEN Year=2006 THEN Month2 ELSE 0 END) AS [2006 Month2],
SUM(CASE WHEN Year=2006 THEN Month3 ELSE 0 END) AS [2006 Month3],
... other columns here
FROM Table
GROUP BY Item[/code]

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-24 : 03:23:13

I will try that.

Year 2008 month 12 is comming and I want the system to add it in the results. I don't want to add the line SUM(CASE WHEN Year=2008 THEN Month12 ELSE 0 END) AS [2008 Month12]. I want a script that detect a new month/year.

Thank you for your help visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 03:35:04

are you using sql 2005?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 03:35:05

are you using sql 2005?

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-24 : 03:39:45

No 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 03:47:28

see this

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-24 : 04:49:32

How do you write it when using SQL 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 04:56:39

sql 2005 has PIVOT operator which makes this easier.

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-24 : 05:11:27

can you give me an example please. I have got a different databse on SQL 2005. I can't test it on there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 05:18:21

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-11-27 : 02:04:57

Select item,
sum( case year when 2005 then month1 else 0 end),
sum( case year when 2005 then month2 else 0 end),
sum( case year when 2005 then month3 else 0 end),
sum( case year when 2006 then month1 else 0 end) ,
sum( case year when 2006 then month2 else 0 end)
from Items
Group By item

I Struggle For Excellence

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-27 : 02:05:50

SELECT pvt.item ,pvt.[2005] AS '2005month1', pvt1.[2005] AS '2005month2',pvt2.[2005] AS '2005month3',pvt3.[2006] AS '2006month1',pvt4.[2006] AS '2006month2',pvt5.[2006] AS '2006month3'
FROM
(SELECT item,year,Month1,Month2,Month3 FROM items)M
PIVOT (MAX(month1) FOR year IN
([2005] ))AS PVT
INNER JOIN
(SELECT item,year,Month1,Month2,Month3 FROM items)M
PIVOT (MAX(month2) FOR year IN
([2005] ))AS PVT1 ON pvt.item = pvt1.item
INNER JOIN
(SELECT item,year,Month1,Month2,Month3 FROM items)M
PIVOT (MAX(month3) FOR year IN
([2005] ))AS PVT2 ON pvt2.item = pvt1.item
INNER JOIN
(SELECT item,year,Month1,Month2,Month3 FROM items)M
PIVOT (MAX(month1) FOR year IN
([2006] ))AS PVT3 ON pvt3.item = pvt1.item
INNER JOIN
(SELECT item,year,Month1,Month2,Month3 FROM items)M
PIVOT (MAX(month2) FOR year IN
([2006] ))AS PVT4 ON pvt4.item = pvt1.item
INNER JOIN
(SELECT item,year,Month1,Month2,Month3 FROM items)M
PIVOT (MAX(month3) FOR year IN
([2006] ))AS PVT5 ON pvt5.item = pvt1.item
WHERE pvt.[2005] IS NOT NULL AND pvt1.[2005] IS NOT NULL AND PVT2.[2005] IS NOT NULL
AND pvt3.[2006] IS NOT NULL AND pvt4.[2006] IS NOT NULL AND PVT5.[2006] IS NOT NULL

By Using Pivot
Try it in SQL 2005

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-11-27 : 02:07:16

SELECT item,
SUM(CASE year WHEN 2005 THEN month1 ELSE 0 END) AS '2005 month1',
SUM(CASE year WHEN 2005 THEN month2 ELSE 0 END) AS '2005 month2',
SUM(CASE year WHEN 2005 THEN month3 ELSE 0 END) AS '2005 month3',
SUM(CASE year WHEN 2006 THEN month1 ELSE 0 END) AS '2006 month1',
SUM(CASE year WHEN 2006 THEN month2 ELSE 0 END) AS '2006 month2',
SUM(CASE year WHEN 2006 THEN month3 ELSE 0 END) AS '2006 month3',
SUM(CASE year WHEN 2007 THEN month1 ELSE 0 END) AS '2007 month1',
SUM(CASE year WHEN 2007 THEN month2 ELSE 0 END) AS '2007 month2',
SUM(CASE year WHEN 2007 THEN month3 ELSE 0 END) AS '2007 month3'
FROM items
GROUP BY item

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 02:30:20

quote:
Originally posted by Nageswar9

Select item,
sum( case year when 2005 then month1 else 0 end),
sum( case year when 2005 then month2 else 0 end),
sum( case year when 2005 then month3 else 0 end),
sum( case year when 2006 then month1 else 0 end) ,
sum( case year when 2006 then month2 else 0 end)
from Items
Group By item

I Struggle For Excellence

Visakh suggested this. You just removed column names

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 02:32:50

quote:
Originally posted by Jai Krishna

SELECT item,
SUM(CASE year WHEN 2005 THEN month1 ELSE 0 END) AS '2005 month1',
SUM(CASE year WHEN 2005 THEN month2 ELSE 0 END) AS '2005 month2',
SUM(CASE year WHEN 2005 THEN month3 ELSE 0 END) AS '2005 month3',
SUM(CASE year WHEN 2006 THEN month1 ELSE 0 END) AS '2006 month1',
SUM(CASE year WHEN 2006 THEN month2 ELSE 0 END) AS '2006 month2',
SUM(CASE year WHEN 2006 THEN month3 ELSE 0 END) AS '2006 month3',
SUM(CASE year WHEN 2007 THEN month1 ELSE 0 END) AS '2007 month1',
SUM(CASE year WHEN 2007 THEN month2 ELSE 0 END) AS '2007 month2',
SUM(CASE year WHEN 2007 THEN month3 ELSE 0 END) AS '2007 month3'
FROM items
GROUP BY item

Dont use single quotes around column names. Use [ and ] instead

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 02:33:26

quote:
Originally posted by madhivanan

quote:
Originally posted by Nageswar9

Select item,
sum( case year when 2005 then month1 else 0 end),
sum( case year when 2005 then month2 else 0 end),
sum( case year when 2005 then month3 else 0 end),
sum( case year when 2006 then month1 else 0 end) ,
sum( case year when 2006 then month2 else 0 end)
from Items
Group By item

I Struggle For Excellence

Visakh suggested this. You just removed column names

Madhivanan

Failing to plan is Planning to fail

May be he believed its a new suggestion altogether

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-28 : 08:59:48

It works very cool. But I must ask the following. I have got Year 2005 to 2008 and month 1 to 12. Next year the database will add 2009. Is there a way to make this dynamic? I mean that I don't have to enter the year 2009 lines there?

Regarsd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 09:42:11

quote:
Originally posted by Deon Smit

It works very cool. But I must ask the following. I have got Year 2005 to 2008 and month 1 to 12. Next year the database will add 2009. Is there a way to make this dynamic? I mean that I don't have to enter the year 2009 lines there?

Regarsd

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources