Total column in SQL Dynamic Pivot

• Gordon Barclay

  SSC Eights!

  Points: 922

  More actions

  July 3, 2017 at 3:10 am

  #402241

  I have used normal SQL pivot queries before but realised I needed dynamically created one for this piece of work, I am struggling to include a total value added at the end of the row.  I have attached a text file with both the original SQL and the output of the dynamically generated SQL.

  Any help would be gratefully received, please feel free to ask me any questions that you may have.

  Gordon Barclay

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  July 3, 2017 at 6:01 am

  #1949116

  Here you go:
  --============SQL===================
DECLARE @cols    AS NVARCHAR (MAX),
        @query AS NVARCHAR (MAX);

  SELECT    @cols = STUFF((    SELECT    ',' + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + RIGHT(DATENAME(year,[Month]),2))
                        FROM    ForeCastData...[Data$]
                        WHERE    [Status] = 'Order'
                            AND MONTH([Month]) >= MONTH(GETDATE()) - 1
                        GROUP BY [Month]
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)')
                    , 1, 1,'');

  SET        @query = 'SELECT PRODUCT, ' + @cols + ',
    ' + REPLACE(@cols, ',', ' + ') + ' AS TOTAL
FROM (
        SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
        FROM ForeCastData...[Data$]
    ) AS x
    PIVOT (SUM(QTY) FOR month in (' + @cols + ')) AS p
ORDER BY PRODUCT ASC;'

  EXECUTE (@query);

  --====== Dynamically created SQL result======

  SELECT PRODUCT, [Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17],
    [Jun-17] + [Jul-17] + [Aug-17] + [Sep-17] + [Oct-17] + [Nov-17] + [Dec-17] AS TOTAL
FROM (
        SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
        FROM ForeCastData...[Data$]
    ) AS x
    PIVOT (SUM(QTY) FOR month IN ([Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17])) AS p
ORDER BY PRODUCT ASC;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Gordon Barclay

  SSC Eights!

  Points: 922

  More actions

  July 3, 2017 at 7:06 am

  #1949124

  sgmunson - Monday, July 3, 2017 6:01 AM

  Here you go:
  --============SQL===================
  DECLARE @cols    AS NVARCHAR (MAX),
          @query AS NVARCHAR (MAX);

  SELECT    @cols = STUFF((    SELECT    ',' + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + RIGHT(DATENAME(year,[Month]),2))
                          FROM    ForeCastData...[Data$]
                          WHERE    [Status] = 'Order'
                              AND MONTH([Month]) >= MONTH(GETDATE()) - 1
                          GROUP BY [Month]
                          FOR XML PATH(''), TYPE
                          ).value('.', 'NVARCHAR(MAX)')
                      , 1, 1,'');

  SET        @query = 'SELECT PRODUCT, ' + @cols + ',
      ' + REPLACE(@cols, ',', ' + ') + ' AS TOTAL
  FROM (
          SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
          FROM ForeCastData...[Data$]
      ) AS x
      PIVOT (SUM(QTY) FOR month in (' + @cols + ')) AS p
  ORDER BY PRODUCT ASC;'

  EXECUTE (@query);

  --====== Dynamically created SQL result======

  SELECT PRODUCT, [Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17],
    [Jun-17] + [Jul-17] + [Aug-17] + [Sep-17] + [Oct-17] + [Nov-17] + [Dec-17] AS TOTAL
FROM (
        SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
        FROM ForeCastData...[Data$]
    ) AS x
    PIVOT (SUM(QTY) FOR month IN ([Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17])) AS p
ORDER BY PRODUCT ASC;

  Not all the rows are totalling however, I think this is because of NULL values where qty value for that period exists, anyone wish to offer an opinion?

  Gordon Barclay

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 3, 2017 at 7:29 am

  #1949133

  This is one of the reasons I prefer to use cross tabs.
  
DECLARE @cols    AS NVARCHAR (MAX),
            @query AS NVARCHAR (MAX)
SELECT    @cols = STUFF((SELECT ',SUM( CASE WHEN Month = ' + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2), '''')
          + 'THEN QTY ELSE 0 END) AS ' + + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)) +CHAR(10)
                        FROM    ForeCastData...[Data$]
                        WHERE    1 = 1
                        AND        [Status] = 'Order'
                        AND        MONTH([Month])>= MONTH(GETDATE()) - 1
                        GROUP BY [Month]
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,'')

  SET        @query = 'SELECT product,' + @cols + ' , SUM(QTY) AS GrandTotal
      FROM
                 (
                    SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2) AS [Month], SUM(ISNULL(QTY,0)) AS QTY
                    FROM ForeCastData...[Data$]
       GROUP BY PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2)
                ) x
                GROUP BY PRODUCT
                ORDER BY 1 ASC'

  EXECUTE (@query)


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Gordon Barclay

  SSC Eights!

  Points: 922

  More actions

  July 3, 2017 at 7:50 am

  #1949140

  Luis Cazares - Monday, July 3, 2017 7:29 AM

  This is one of the reasons I prefer to use cross tabs.
  
  DECLARE @cols    AS NVARCHAR (MAX),
              @query AS NVARCHAR (MAX)
  SELECT    @cols = STUFF((SELECT ',SUM( CASE WHEN Month = ' + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2), '''')
            + 'THEN QTY ELSE 0 END) AS ' + + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)) +CHAR(10)
                          FROM    ForeCastData...[Data$]
                          WHERE    1 = 1
                          AND        [Status] = 'Order'
                          AND        MONTH([Month])>= MONTH(GETDATE()) - 1
                          GROUP BY [Month]
                          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,'')

  SET        @query = 'SELECT product,' + @cols + ' , SUM(QTY) AS GrandTotal
        FROM
                   (
                      SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2) AS [Month], SUM(ISNULL(QTY,0)) AS QTY
                      FROM ForeCastData...[Data$]
         GROUP BY PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2)
                  ) x
                  GROUP BY PRODUCT
                  ORDER BY 1 ASC'

  EXECUTE (@query)


  I did try to do this via cross tab but could not get the column headers to be created dynamically e.g.  I would need the headers to say Jan-17, Feb-17, Mar-17..etc.  These would need to change when the year did e.g. Nov-17, Dec-17, Jan-18

  SELECT distinct product as 'Product',
    SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jan-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jan,
    SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Feb-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Feb,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Mar-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Mar,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Apr-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Apr,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='May-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) May,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jun-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jun,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jul-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jul,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Aug-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Aug,
      SUM(QTY) AS Total
  FROM     ForeCastData...[Data$]
      WHERE    1 = 1
      AND        [Status] = 'Order'
      AND        [Month] BETWEEN DATEADD(MONTH, -4, GETDATE()) AND DATEADD(MONTH, +5, GETDATE())
  GROUP BY product
  ORDER BY product ASC

  Gordon Barclay

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  July 3, 2017 at 9:20 am

  #1949185

  Gordon Barclay - Monday, July 3, 2017 7:06 AM

  sgmunson - Monday, July 3, 2017 6:01 AM

  Here you go:
  --============SQL===================
  DECLARE @cols    AS NVARCHAR (MAX),
          @query AS NVARCHAR (MAX);

  SELECT    @cols = STUFF((    SELECT    ',' + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + RIGHT(DATENAME(year,[Month]),2))
                          FROM    ForeCastData...[Data$]
                          WHERE    [Status] = 'Order'
                              AND MONTH([Month]) >= MONTH(GETDATE()) - 1
                          GROUP BY [Month]
                          FOR XML PATH(''), TYPE
                          ).value('.', 'NVARCHAR(MAX)')
                      , 1, 1,'');

  SET        @query = 'SELECT PRODUCT, ' + @cols + ',
      ' + REPLACE(@cols, ',', ' + ') + ' AS TOTAL
  FROM (
          SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
          FROM ForeCastData...[Data$]
      ) AS x
      PIVOT (SUM(QTY) FOR month in (' + @cols + ')) AS p
  ORDER BY PRODUCT ASC;'

  EXECUTE (@query);

  --====== Dynamically created SQL result======

  SELECT PRODUCT, [Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17],
    [Jun-17] + [Jul-17] + [Aug-17] + [Sep-17] + [Oct-17] + [Nov-17] + [Dec-17] AS TOTAL
FROM (
        SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
        FROM ForeCastData...[Data$]
    ) AS x
    PIVOT (SUM(QTY) FOR month IN ([Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17])) AS p
ORDER BY PRODUCT ASC;

  Not all the rows are totalling however, I think this is because of NULL values where qty value for that period exists, anyone wish to offer an opinion?

  If it's just a matter of NULL values, and performance is NOT an issue, then this should do it:
  --============SQL===================
DECLARE @cols  AS NVARCHAR (MAX),
   @query AS NVARCHAR (MAX);

  SELECT  @cols = STUFF((  SELECT  ',' + QUOTENAME(LEFT(DATENAME(MONTH,[Month]),3) + '-' + RIGHT(DATENAME(year,[Month]),2))
        FROM  ForeCastData...[Data$]
        WHERE  [Status] = 'Order'
          AND MONTH([Month]) >= MONTH(GETDATE()) - 1
        GROUP BY [Month]
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
       , 1, 1,'');

  SET   @query = 'SELECT PRODUCT, ' + @cols + ',
  ISNULL(' + REPLACE(@cols, ',', ',0.) + ISNULL(') + ',0.) AS TOTAL
FROM (
   SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + ''-'' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
   FROM ForeCastData...[Data$]
  ) AS x
  PIVOT (SUM(QTY) FOR month in (' + @cols + ')) AS p
ORDER BY PRODUCT ASC;'

  EXECUTE (@query);

  --====== Dynamically created SQL result======

  SELECT PRODUCT, [Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17],
  ISNULL([Jun-17], 0.) + ISNULL([Jul-17], 0.) + ISNULL([Aug-17], 0.) + ISNULL([Sep-17], 0.) + ISNULL([Oct-17], 0.) + ISNULL([Nov-17], 0.) + ISNULL([Dec-17], 0.) AS TOTAL
FROM (
   SELECT PRODUCT, LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2) AS [Month], ISNULL(QTY,0) AS QTY
   FROM ForeCastData...[Data$]
  ) AS x
  PIVOT (SUM(QTY) FOR month IN ([Jun-17],[Jul-17],[Aug-17],[Sep-17],[Oct-17],[Nov-17],[Dec-17])) AS p
ORDER BY PRODUCT ASC;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 3, 2017 at 10:15 am

  #1949195

  Gordon Barclay - Monday, July 3, 2017 7:50 AM

  I did try to do this via cross tab but could not get the column headers to be created dynamically e.g.  I would need the headers to say Jan-17, Feb-17, Mar-17..etc.  These would need to change when the year did e.g. Nov-17, Dec-17, Jan-18

  SELECT distinct product as 'Product',
    SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jan-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jan,
    SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Feb-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Feb,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Mar-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Mar,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Apr-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Apr,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='May-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) May,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jun-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jun,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jul-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jul,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Aug-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Aug,
      SUM(QTY) AS Total
  FROM     ForeCastData...[Data$]
      WHERE    1 = 1
      AND        [Status] = 'Order'
      AND        [Month] BETWEEN DATEADD(MONTH, -4, GETDATE()) AND DATEADD(MONTH, +5, GETDATE())
  GROUP BY product
  ORDER BY product ASC

  Is that from the query I posted? It shouldn't be and it shouldn't have that problem.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Gordon Barclay

  SSC Eights!

  Points: 922

  More actions

  July 3, 2017 at 10:55 am

  #1949205

  Luis Cazares - Monday, July 3, 2017 10:15 AM

  Gordon Barclay - Monday, July 3, 2017 7:50 AM

  I did try to do this via cross tab but could not get the column headers to be created dynamically e.g.  I would need the headers to say Jan-17, Feb-17, Mar-17..etc.  These would need to change when the year did e.g. Nov-17, Dec-17, Jan-18

  SELECT distinct product as 'Product',
    SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jan-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jan,
    SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Feb-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Feb,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Mar-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Mar,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Apr-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Apr,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='May-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) May,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jun-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jun,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Jul-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Jul,
      SUM(Case When LEFT(DATENAME(MONTH,[Month]),3) + '-' + Right(DATENAME(year,[Month]),2)='Aug-' + CAST(Right(DATENAME(year,[Month]),2) AS CHAR(2)) Then QTY Else 0 End) Aug,
      SUM(QTY) AS Total
  FROM     ForeCastData...[Data$]
      WHERE    1 = 1
      AND        [Status] = 'Order'
      AND        [Month] BETWEEN DATEADD(MONTH, -4, GETDATE()) AND DATEADD(MONTH, +5, GETDATE())
  GROUP BY product
  ORDER BY product ASC

  Is that from the query I posted? It shouldn't be and it shouldn't have that problem.

  No, this was from my own attempt at doing this using crosstab.  I added this for information purposes only.

  Gordon Barclay