SQL unpivoting columns

How to unpivot table:

+------+-----+------+--------------------------------------------------------------+----+----+----+----+----+----+----+----+----+-----+-----+-----+
| YEAR | SEC | DEPT |                            TITLE                             | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
+------+-----+------+--------------------------------------------------------------+----+----+----+----+----+----+----+----+----+-----+-----+-----+
| 2002 | C   |   30 | Other transport equipment                                    |  1 |  1 |  1 |  1 |  1 |  1 |  1 |  1 |  1 |   1 |   1 |   1 |
| 2002 | C   |   31 | Furniture manufacturing                                      |  2 |  2 |  2 |  2 |  2 |  2 |  2 |  2 |  2 |   2 |   2 |   2 |
| 2002 | C   |   32 | Other manufacturing                                          |  3 |  3 |  3 |  3 |  3 |  3 |  3 |  3 |  3 |   3 |   3 |   3 |
| 2002 | D   |   30 | Repair and installation of machinery and equipment           |  4 |  4 |  4 |  4 |  4 |  4 |  4 |  4 |  4 |   4 |   4 |   4 |
| 2002 | D   |   31 | Electricity, gas, steam and air conditioning                 |  5 |  5 |  5 |  5 |  5 |  5 |  5 |  5 |  5 |   5 |   5 |   5 |
| 2002 | D   |   32 | Water collection, treatment and supply                       |  6 |  6 |  6 |  6 |  6 |  6 |  6 |  6 |  6 |   6 |   6 |   6 |
| 2002 | E   |   30 | Waste collection, treatment and disposal; materials recovery |  7 |  7 |  7 |  7 |  7 |  7 |  7 |  7 |  7 |   7 |   7 |   7 |
| 2002 | E   |   31 | Remediation activities and other waste management services   |  8 |  8 |  8 |  8 |  8 |  8 |  8 |  8 |  8 |   8 |   8 |   8 |
| 2002 | E   |   32 | Construction of buildings                                    |  9 |  9 |  9 |  9 |  9 |  9 |  9 |  9 |  9 |   9 |   9 |   9 |
+------+-----+------+--------------------------------------------------------------+----+----+----+----+----+----+----+----+----+-----+-----+-----+

to this:

+------+-------+-----+------+---------------------------+-------+
| YEAR | MONTH | SEC | DEPT |           TITLE           | COUNT |
+------+-------+-----+------+---------------------------+-------+
| 2002 |     1 | C   |   30 | Other transport equipment |     1 |
| 2002 |     1 | C   |   31 | Furniture manufacturing   |     2 |
| 2002 |     1 | C   |   32 | Other manufacturing       |     3 |
| 2002 |     2 | C   |   30 | Other transport equipment |     1 |
| 2002 |     2 | C   |   31 | Furniture manufacturing   |     2 |
| 2002 |     2 | C   |   32 | Other manufacturing       |     3 |
+------+-------+-----+------+---------------------------+-------+

What I have so far is :

select YEAR, SEC, DEPT,TITLE, MONTH
from mytable u
unpivot
(
  MONTH for mj in ([M1]
      ,[M2]
      ,[M3]
      ,[M4]
      ,[M5]
      ,[M6]
      ,[M7]
      ,[M8]
      ,[M9]
      ,[M10]
      ,[M11]
      ,[M12] )
) u;

but this is giving me count for all months. I need this with month number and count value for that month. Values 1-9 could be any numbers.

Answer

Got it working with

    SELECT YEAR, SEC, DEPT,TITLE, MONTH, TOTAL
FROM 
(SELECT YEAR, SEC, DEPT,TITLE
      ,M1
      ,M2
      ,M3
      ,M4
      ,M5
      ,M6
      ,M7
      ,M8
      ,M9
      ,M10
      ,M11
      ,M12
FROM db.dbo.mytable pvt) p
UNPIVOT
    (TOTAL FOR MONTH IN
    ( M1
      ,M2
      ,M3
      ,M4
      ,M5
      ,M6
      ,M7
      ,M8
      ,M9
      ,M10
      ,M11
      ,M12)) AS unpvt;

Advertisement

Answer