Query Help Pivot
-
My db version SQL Server 2008.
CREATE TABLE [dbo].[exp](
[day] [int] NULL,
[budgethead] [int] NULL,
[amount] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (3, 4, 100)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (1, 10, 270)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (3, 7, 35)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (5, 4, 125)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (8, 12, 24)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (2, 7, 40)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (6, 1, 22)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (2, 7, 45)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (10, 4, 127)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (1, 10, 45)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (2, 4, 145)
INSERT [dbo].[exp] ([day], [budgethead], [amount]) VALUES (5, 7, 200)Required Output :
Day1 Amt1 Day2 Amt2 Day3 Amt3 Day4 Amt4 Day5 Amt5 Day6 Amt6 Day7 Amt7 Day8 Amt8 Day9 Amt9 Day10 Amt10
10 315 4 145 4 100 0 0 4 125 1 22 0 0 12 24 0 0 4 127
0 0 7 85 0 0 0 0 7 200 0 0 0 0 0 0 0 0 0 0 -
Try this
with src as (
select day,budgethead,sum(amount) as amount,row_number() over(partition by day order by budgethead) as rn
from [dbo].[exp]
group by day,budgethead
)
select isnull(max(case when day=1 then budgethead end),0) as Day1,
isnull(max(case when day=1 then amount end),0) as Amt1,
isnull(max(case when day=2 then budgethead end),0) as Day2,
isnull(max(case when day=2 then amount end),0) as Amt2,
isnull(max(case when day=3 then budgethead end),0) as Day3,
isnull(max(case when day=3 then amount end),0) as Amt3,
isnull(max(case when day=4 then budgethead end),0) as Day4,
isnull(max(case when day=4 then amount end),0) as Amt4,
isnull(max(case when day=5 then budgethead end),0) as Day5,
isnull(max(case when day=5 then amount end),0) as Amt5,
isnull(max(case when day=6 then budgethead end),0) as Day6,
isnull(max(case when day=6 then amount end),0) as Amt6,
isnull(max(case when day=7 then budgethead end),0) as Day7,
isnull(max(case when day=7 then amount end),0) as Amt7,
isnull(max(case when day=8 then budgethead end),0) as Day8,
isnull(max(case when day=8 then amount end),0) as Amt8,
isnull(max(case when day=9 then budgethead end),0) as Day9,
isnull(max(case when day=9 then amount end),0) as Amt9,
isnull(max(case when day=10 then budgethead end),0) as Day10,
isnull(max(case when day=10 then amount end),0) as Amt10
from src
group by rn
order by rn;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks alot Mark Cowne. I got the output as I requested.
Once again, Thank you very much.
-
The question now is... do you understand how and why it works? Also, for future reference, this ancient but incredibly effective method is known as a "CROSSTAB". It used to be included in "Books Online" but they dumped all reference to it when PIVOT came out.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply