Re arranging table for reporting
-
Hello,
I have a table like the following:
ID Month Year In1 In2 In3
A 1 2004 60 20 30
B 1 2004 50 10 40
C 1 2004 30 20 10
A 2 2004 30 11 20
B 2 2004 60 20 30
C 2 2004 10 90 45
B 3 2004 20 10 30
C 3 2004 60 20 30
D 3 2004 60 20 30
This month I can have an entry for ID=A and next month A might not be there and a new ID might show up (in Month=3 there is no A and D is the new ID). What I am trying to get to a table like the following:
ID In Year Jan Feb Mar ...... Dec
A 1 2004 60 30
A 2 2004 20 11
A 3 2004 30 20
B 1 2004 50 60 20
B 2 2004 10 20 10
B 3 2004 40 30 30
D 1 2004 60
D 2 2004 20
D 3 2004 30
Where In is In1, In2 and In3 from the first table. So far I am able to create the following:
ID In Year Jan Feb Mar ...... Dec
A 1 2004
A 2 2004
A 3 2004
B 1 2004
B 2 2004
B 3 2004
D 1 2004
D 2 2004
D 3 2004
Any suggestions on what the best was is to populate the data. Any help would be greatly appreciated.
Thank you.
-
You have 2 pivots to perform. To translate Column names into data values, you can use a UNION query. This converts the In1, In2 and In3 column into values within a column named "In":
Select ID, Month, 1 As In, In1 As Amount
From YourTable
Union
Select ID, Month, 2 As In, In2 As Amount
From YourTable
Union
Select ID, Month, 3 As In, In3 As Amount
From YourTable
The 2nd step is to pivot column values into column names. A common method for this is a series of CASE ... WHEN evaluations:
Select ID, In, Year,
Sum ( Case Month When 1 Then Amount Else 0 End ) As Jan,
Sum ( Case Month When 2 Then Amount Else 0 End ) As Feb,
...
Sum ( Case Month When 12 Then Amount Else 0 End ) As Dec
From
(
Select ID, Month, Year, 1 As In, In1 As Amount
From YourTable
Union
Select ID, Month, Year, 2 As In, In2 As Amount
From YourTable
Union
Select ID, Month, Year, 3 As In, In3 As Amount
From YourTable
) vtable
Group By ID, In, Year
-
Journeyman,
I ran the query in query analyzer and it worked perfectly. Thank you very much. I should be able to do:
insert into myTable(<fields>
<select query from your response>
Thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply