Transpose columns to rows

[johnhugh]

In SQL Server 2005, how could I query my table so the results are not shown as

Year|Month1|Month2|Month3|...|Month12

Instead they should be shown as:

Year| Month1
Year| Month2
Year| Month3
...

 

[Rhys666]

you'll hate me for it but...

SELECT Year, 1 AS MONTH, Month1 AS MONTH_VALUE
FROM YourTable
WHERE Month1 IS NOT NULL
UNION
SELECT Year, 2 AS MONTH, Month2 AS MONTH_VALUE
FROM YourTable
WHERE Month2 IS NOT NULL
UNION
SELECT Year, 3 AS MONTH, Month3 AS MONTH_VALUE
FROM YourTable
WHERE Month3 IS NOT NULL
...

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[johnhugh]

Thanks Rhys!
On another side I found an example for UNPIVOT which I've use to create my query.
It seems to run much faster than a query with UNION.

Only thing I hav enot managed yet is to show a number for the month. What you do with "1 AS MONTH".
In my query it shows NETPERD1, NETPERD2 instead.

Would you know how to get the month number in my query also?

SELECT     tblpivot.FYear, tblpivot.Account, tblpivot.property as FMonth, tblpivot.value as Balance
FROM         (SELECT     FSCSYR AS FYear, ACCTID AS Account, OPENBAL, NETPERD1, NETPERD2, NETPERD3, NETPERD4, NETPERD5, NETPERD6, NETPERD7, NETPERD8, 
                                              NETPERD9, NETPERD10, NETPERD11, NETPERD12, NETPERD13, NETPERD14, NETPERD15
                       FROM          GLAFS
                       WHERE      FSCSDSG = 'A' AND CURNTYPE = 'F' AND 
                                              (OPENBAL + NETPERD1 + NETPERD2 + NETPERD3 + NETPERD4 + NETPERD5 + NETPERD6 + NETPERD7 + NETPERD8 + NETPERD9 + NETPERD10 + NETPERD11
                                               + NETPERD12 + NETPERD13 + NETPERD14 + NETPERD15 <> 0)) t UNPIVOT (value FOR property IN (OPENBAL, NETPERD1, NETPERD2, NETPERD3, 
                      NETPERD4, NETPERD5, NETPERD6, NETPERD7, NETPERD8, NETPERD9, NETPERD10, NETPERD11, NETPERD12, NETPERD13, NETPERD14, NETPERD15)) 
                      AS tblpivot

 

[gmmastros]

johnhugh,

Try Rhys's suggestion again, but replace UNION with UNION ALL. UNION ALL should be faster.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnhugh]

Thanks, but not really. Same time.
UNPIVOT is quicker by factor 10.