CODEWITHSUNDEEP
sql-servert-sql
Jules 999
Jules 999

Reputation: 145

SQL Server PIVOT problem

For the life of me I can't see what I am doing wrong here.

SELECT Series_ID
       , 1 AS Jan
       , 2 AS Feb
       , 3 AS Mar
       , 4 AS Apr
       , 5 AS May
       , 6 AS Jun
       , 7 AS Jul
       , 8 AS Aug
       , 9 AS Sep
       , 10 AS Oct
       , 11 AS Nov
       , 12 AS [Dec]
FROM 
  (
    SELECT MONTH(ActionDate_DT) AS [MonthNum]
         , Series_ID
         , ID
      FROM TasksSeries_V
     WHERE Series_ID IN (5632751,5672397,5680669,5680670,5632713,5632752,5680902)
           AND  TasksSeries_V.ActionDate_DT BETWEEN '01 Jan 2011' AND '31 Dec 2011'
  ) AS SourceTable
PIVOT 
  (
COUNT(ID) FOR MonthNum IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
  ) AS pvt

When I run this I just get

ID      Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
5632713 1   2   3   4   5   6   7   8   9   10  11  12
5632751 1   2   3   4   5   6   7   8   9   10  11  12
5632752 1   2   3   4   5   6   7   8   9   10  11  12
5672396 1   2   3   4   5   6   7   8   9   10  11  12
5672397 1   2   3   4   5   6   7   8   9   10  11  12
5680669 1   2   3   4   5   6   7   8   9   10  11  12
5680670 1   2   3   4   5   6   7   8   9   10  11  12
5680902 1   2   3   4   5   6   7   8   9   10  11  12

Any ideas?

Upvotes: 0

Views: 266

Answers (2)

billinkc
billinkc

Reputation: 61211

The line

SELECT Series_ID, 1 AS Jan, 2 AS Feb, 3 AS Mar, 4 AS Apr, 5 AS May, 6 AS Jun, 7 AS Jul, 8 AS Aug, 9 AS Sep, 10 AS Oct, 11 AS Nov, 12 AS [Dec]

Indicates that we should select the literal value 1 and alias as Jan, literal value 2 and alias as Feb... Instead, reference the column named 1 by wrapping in square brackets or double quotes

SELECT Series_ID, [1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr, [5] AS May, [6] AS Jun, [7] AS Jul, [8] AS Aug, [9] AS Sep, [10] AS Oct, [11] AS Nov, [12] AS [Dec]

Upvotes: 0

Mike Dinescu
Mike Dinescu

Reputation: 55720

I think the problem is that in the first SELECT statement you sould use [1] instead of 1 for the column names..

SELECT Series_ID
       , [1] AS Jan
       , [2] AS Feb
       , [3] AS Mar
       , [4] AS Apr
       , [5] AS May
       , [6] AS Jun
       , [7] AS Jul
       , [8] AS Aug
       , [9] AS Sep
       , [10] AS Oct
       , [11] AS Nov
       , [12] AS [Dec]
FROM 
  (
    SELECT MONTH(ActionDate_DT) AS [MonthNum]
         , Series_ID
         , ID
      FROM TasksSeries_V
     WHERE Series_ID IN (5632751,5672397,5680669,5680670,5632713,5632752,5680902)
           AND  TasksSeries_V.ActionDate_DT BETWEEN '01 Jan 2011' AND '31 Dec 2011'
  ) AS SourceTable
PIVOT 
  (
      COUNT(ID) 
        FOR MonthNum IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
  ) AS pvt

Upvotes: 3

Related Questions