CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
gmang
gmang

Reputation: 435

PIVOT SQL - How to display the column we are pivoting on?

I have written my pivot SQL and it is working. The current SQL Statement is below and I will show an example of how the data is returned. I need to add one column and this is where it bombs:

Select 
    ProductGroup,
    Origin,
    Destination,
    [YEAR], 
    Isnull([Jan],0) as "Jan",
    isnull([Feb],0) as "Feb",
    isnull([Mar],0) as "Mar",
    isnull([Apr],0) as "Apr",
    isnull([May],0) as "May",
    isnull([Jun],0) as "Jun",
    isnull([Jul],0) as "Jul",
    isnull([Aug],0) as "Aug",
    isnull([Sep],0) as "Sep",
    isnull([Oct],0) as "Oct",
    isnull([Nov],0) as "Nov",
    isnull([Dec],0) as "Dec"
FROM
(
SELECT 
    p.ProductGroup, 
    S.Origin, 
    S.FinalDest AS Destination,
    SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec', (Month(boldate) * 4) - 3, 3) as MonthAbreviated, 
    Year(BolDate) AS [Year], 
    Count(*) AS [Total]
--  ,COUNT(S.Purchase#) AS [TheTotal]
    FROM 
        dbo.Contracts c INNER JOIN dbo.Purchases pu ON c.[Contract#] = pu.[Contract#]
        INNER JOIN dbo.Products as p ON pu.Product = p.Product 
        INNER JOIN dbo.Shipments S ON pu.[Purchase#] = S.[Purchase#]
WHERE 
    Year(BolDate)<>1994 AND
    pu.Cancelled=0 AND S.[Status]='L'
GROUP BY p.ProductGroup, S.Origin, S.FinalDest,Year(BolDate), SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec', (Month(boldate) * 4) - 3, 3)

) AS SourceTable
PIVOT
(
sum( Total )
FOR MonthAbreviated IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) AS PivotTable

EXAMPLE of results:

ProductGroup   Origin  Destination  YEAR        Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 
Nail           Bath    Toronto      2012        0   0   0   10  1   0   0   0   0   0   0   0 
Nail           Cedars  Toronto      2011        0   0   0   0   0   0   0   0   0   0   25  53

I need to add a column that displays the total of the row. For example row 1 is 11 and row 2 is 78. In my select, I thought simply adding "Total" to the query would do it but I get an invalid column each time.

In other words I get this error:

Msg 207, Level 16, State 1, Line 6 Invalid column name 'Total'.

Select 
    ProductGroup,
    Origin,
    Destination,
    [YEAR], 
    [Total],
    Isnull([Jan],0) as "Jan",
    isnull([Feb],0) as "Feb",
    isnull([Mar],0) as "Mar",
    isnull([Apr],0) as "Apr",
    isnull([May],0) as "May",
    isnull([Jun],0) as "Jun",
    isnull([Jul],0) as "Jul",
    isnull([Aug],0) as "Aug",
    isnull([Sep],0) as "Sep",
    isnull([Oct],0) as "Oct",
    isnull([Nov],0) as "Nov",
    isnull([Dec],0) as "Dec"
FROM
(
SELECT 
    p.ProductGroup, 
    S.Origin, 
    S.FinalDest AS Destination,
    SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec', (Month(boldate) * 4) - 3, 3) as MonthAbreviated, 
    Year(BolDate) AS [Year], 
    Count(*) AS [Total]
--  ,COUNT(S.Purchase#) AS [TheTotal]
    FROM 
        dbo.Contracts c INNER JOIN dbo.Purchases pu ON c.[Contract#] = pu.[Contract#]
        INNER JOIN dbo.Products as p ON pu.Product = p.Product 
        INNER JOIN dbo.Shipments S ON pu.[Purchase#] = S.[Purchase#]
WHERE 
    Year(BolDate)<>1994 AND
    pu.Cancelled=0 AND S.[Status]='L'
GROUP BY p.ProductGroup, S.Origin, S.FinalDest,Year(BolDate), SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec', (Month(boldate) * 4) - 3, 3)

) AS SourceTable
PIVOT
(
sum( Total )
FOR MonthAbreviated IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) AS PivotTable

Upvotes: 1

Views: 2069

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52675

As an alternative you could also use ROLLUP

You'd need to

  • Add a case statement to give a "name" to the rollup

     CASE WHEN (GROUPING(Substring(DateName(m,boldate),1,3) = 1) Then 'Total'
      ELSE Substring(DateName(m,boldate),1,3) 
 END as MonthAbreviated,

  • Add RollUp to the group by

     GROUP BY
  ....
  Substring(DateName(m,boldate),1,3) 
  WITH ROLLUP

  • Include it in your FOR clause

     ... [Nov],[Dec], [Total])

  • Exclude all other rollup groupings in the Having

          HAVING
         GROUPING (Year(BolDate) = 0)

Note I've modified your MonthAbreviated syntax to shorten it and I had to change the alias for Count(*) since it had the same name as the pivoted value

Select 
    ProductGroup,
    Origin,
    Destination,
    [YEAR], 
    Isnull([Jan],0) as "Jan",
    isnull([Feb],0) as "Feb",
    isnull([Mar],0) as "Mar",
    isnull([Apr],0) as "Apr",
    isnull([May],0) as "May",
    isnull([Jun],0) as "Jun",
    isnull([Jul],0) as "Jul",
    isnull([Aug],0) as "Aug",
    isnull([Sep],0) as "Sep",
    isnull([Oct],0) as "Oct",
    isnull([Nov],0) as "Nov",
    isnull([Dec],0) as "Dec",
    isnull([Total],0) as "Total"
FROM
(
SELECT 
    p.ProductGroup, 
    S.Origin, 
    S.FinalDest AS Destination,
    CASE WHEN (GROUPING(Substring(DateName(m,boldate),1,3) = 1) Then 'Total'
         ELSE Substring(DateName(m,boldate),1,3) 
    END as MonthAbreviated, 
    Year(BolDate) AS [Year], 
    Count(*) AS [Kount]
    FROM 
        dbo.Contracts c INNER JOIN dbo.Purchases pu ON c.[Contract#] = pu.[Contract#]
        INNER JOIN dbo.Products as p ON pu.Product = p.Product 
        INNER JOIN dbo.Shipments S ON pu.[Purchase#] = S.[Purchase#]
WHERE 
    Year(BolDate)<>1994 AND
    pu.Cancelled=0 AND S.[Status]='L'
GROUP BY 
     p.ProductGroup, 
     S.Origin, 
     S.FinalDest,Year(BolDate), 
     Substring(DateName(m,boldate),1,3) 
     WITH ROLLUP
HAVING
    GROUPING (Year(BolDate) = 0)
    

) AS SourceTable
PIVOT
(
sum( Kount )
FOR MonthAbreviated IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec], [Total])
) AS PivotTable

Here's a demo of this technique on data.se

Upvotes: 1

Taryn
Taryn

Reputation: 247810

If you want the Total for the row, you can simply add the columns together to get the total. So you will want to add the following line:

([Jan] + [Feb] + [Mar] + [Apr] + [May] + [Jun] + [Jul] + [Aug] + [Sep] + [Oct] + [Nov] + [Dec]) as Total

making your query:

Select 
    ProductGroup,
    Origin,
    Destination,
    [YEAR], 
    Isnull([Jan],0) as "Jan",
    isnull([Feb],0) as "Feb",
    isnull([Mar],0) as "Mar",
    isnull([Apr],0) as "Apr",
    isnull([May],0) as "May",
    isnull([Jun],0) as "Jun",
    isnull([Jul],0) as "Jul",
    isnull([Aug],0) as "Aug",
    isnull([Sep],0) as "Sep",
    isnull([Oct],0) as "Oct",
    isnull([Nov],0) as "Nov",
    isnull([Dec],0) as "Dec",
    ([Jan] + [Feb] + [Mar] + [Apr] + [May] + [Jun] + [Jul] + [Aug] + [Sep] + [Oct] + [Nov] + [Dec]) as Total
FROM
(
SELECT 
    p.ProductGroup, 
    S.Origin, 
    S.FinalDest AS Destination,
    SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec', (Month(boldate) * 4) - 3, 3) as MonthAbreviated, 
    Year(BolDate) AS [Year], 
    Count(*) AS [Total]
--  ,COUNT(S.Purchase#) AS [TheTotal]
    FROM 
        dbo.Contracts c INNER JOIN dbo.Purchases pu ON c.[Contract#] = pu.[Contract#]
        INNER JOIN dbo.Products as p ON pu.Product = p.Product 
        INNER JOIN dbo.Shipments S ON pu.[Purchase#] = S.[Purchase#]
WHERE 
    Year(BolDate)<>1994 AND
    pu.Cancelled=0 AND S.[Status]='L'
GROUP BY p.ProductGroup, S.Origin, S.FinalDest,Year(BolDate), SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec', (Month(boldate) * 4) - 3, 3)

) AS SourceTable
PIVOT
(
sum( Total )
FOR MonthAbreviated IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) AS PivotTable

Upvotes: 1

Related Questions