| Author |
 Topic  |
|
|
Deon Smit
Starting Member
47 Posts |
 Posted - 11/24/2008 : 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
India
48106 Posts |
Posted - 11/24/2008 : 02:54:49
|
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 |
 |
|
|
Deon Smit
Starting Member
47 Posts |
Posted - 11/24/2008 : 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
India
48106 Posts |
Posted - 11/24/2008 : 03:35:04
|
| are you using sql 2005? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48106 Posts |
Posted - 11/24/2008 : 03:35:05
|
| are you using sql 2005? |
|
|
|
Deon Smit
Starting Member
47 Posts |
Posted - 11/24/2008 : 03:39:45
|
| No 2000 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48106 Posts |
|
|
Deon Smit
Starting Member
47 Posts |
Posted - 11/24/2008 : 04:49:32
|
| How do you write it when using SQL 2005 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48106 Posts |
Posted - 11/24/2008 : 04:56:39
|
| sql 2005 has PIVOT operator which makes this easier. |
|
|
|
Deon Smit
Starting Member
47 Posts |
Posted - 11/24/2008 : 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
India
48106 Posts |
|
|
Nageswar9
Aged Yak Warrior
India
600 Posts |
Posted - 11/27/2008 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 11/27/2008 : 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
India
333 Posts |
Posted - 11/27/2008 : 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 |
Edited by - Jai Krishna on 11/27/2008 02:24:06 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 11/27/2008 : 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
India
22469 Posts |
Posted - 11/27/2008 : 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
India
48106 Posts |
Posted - 11/27/2008 : 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 - 11/28/2008 : 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
India
48106 Posts |
|
| |
Topic |
|
Not Row to Column But rows+Colyumn to Column
