PIVOT with multiple columns

Question

I am trying to pivot on two columns in SQL Server 2008 on an invoice table. So I have data like the follows:

+--------------+--------+---------+------+
| Invoice Date | Item # | Dollars | Lbs. |
+--------------+--------+---------+------+
| 1/1/14       | A      |       1 |    1 |
| 1/2/14       | B      |       2 |    2 |
| 1/3/14       | A      |       3 |    3 |
| 1/4/14       | B      |       4 |    4 |
| 2/1/14       | A      |       5 |    5 |
| 2/1/14       | B      |       6 |    6 |
+--------------+--------+---------+------+

I would like to display it as

+--------+--------------+-----------------+--------------+-----------------+
| Item # | 1/31/14 Lbs. | 1/31/14 Dollars | 2/28/14 Lbs. | 2/28/14 Dollars |
+--------+--------------+-----------------+--------------+-----------------+
| A      |            4 |               4 |            5 |               5 |
| B      |            6 |               6 |            6 |               6 |
+--------+--------------+-----------------+--------------+-----------------+

Note the column name is the last day of that month and either dollars or pounds. I can do it just fine one column (either pounds or dollars) however I can't do it on both.

Here is my example code for just pounds:

DECLARE
  @v_Columns VARCHAR(MAX),
  @v_Query VARCHAR(MAX)

  --pivot and delimit values

SELECT @v_Columns = COALESCE(@v_Columns,'[') + convert(varchar(8), InvoiceDate, 1) + ' Lbs.' + '],[' 
FROM 
( SELECT DISTINCT dbo.ufn_GetLastDayOfMonth(InvoiceDate) As InvoiceDate
  FROM Invoice 
  WHERE InvoiceDate BETWEEN @BEGIN_DATE AND @END_DATE
  ORDER BY InvoiceDate

--delete last two chars of string (the ending ',[')

SET @v_Columns = SUBSTRING(@v_Columns, 1, LEN(@v_Columns)-2)
PRINT @v_Columns

--construct sql statement

SET @v_Query = 

'WITH AllOrders (LastInvoiceDate, Item,  Pounds) AS 
(
   SELECT 
    CONVERT(varchar(8), dbo.ufn_GetLastDayOfMonth(Invoice.InvoiceDate), 1) + ''' + ' Lbs.' + ''' As LastInvoiceDate,
    Item, 
    Pounds 
   FROM INVOICE
   WHERE InvoiceDate BETWEEN @BEGIN_DATE AND  @END_DATE 
)
SELECT *
FROM AllOrders
PIVOT
(
  SUM(QuantityShipped)
  FOR LastInvoiceDate  IN (' + @v_Columns + ')
) AS pivotview'

Thank you all in advance!

Answer 1

Here is your sample table

  CREATE TABLE #TEMP([Invoice Date] DATE,[Item #] VARCHAR(10),[DollarS] NUMERIC(10,0),[Lbs.] NUMERIC(10,0))
    INSERT INTO #TEMP VALUES ('1/1/14', 'A',1,1)
    INSERT INTO #TEMP VALUES ('1/2/14', 'B',2,2)
    INSERT INTO #TEMP VALUES ('1/3/14', 'A',3,3)
    INSERT INTO #TEMP VALUES ('1/4/14', 'B',4,4)
    INSERT INTO #TEMP VALUES ('2/1/14', 'A',5,5)
    INSERT INTO #TEMP VALUES ('2/1/14', 'B',6,6)

Now you need to apply UNION ALL(instead of UNPIVOT) and bring columns to row and combine the columns, get the order of columns as Date+LBS/DOLLARS.

SELECT DISTINCT DENSE_RANK() OVER(ORDER BY  CAST(LASTDAY AS DATE),UNIT DESC)RNO,*,
CAST(DATEPART(MONTH,LASTDAY)AS VARCHAR) +'/'+ CAST(DATEPART(DAY,LASTDAY)AS VARCHAR) +'/' +RIGHT(CAST(YEAR(LASTDAY)AS VARCHAR),2)+' ' +UNIT  PIVOTCOL 
INTO #NEWTABLE 
FROM
(
    SELECT [Item #],'DOLLARS' UNIT,
    DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[Invoice Date])+1,0))LASTDAY,
    SUM([Dollars]) OVER(PARTITION BY [Item #],DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[Invoice Date])+1,0))) VALUE
    FROM #TEMP

    UNION ALL

    SELECT [Item #], 'LBS.',
    DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[Invoice Date])+1,0))LASTDAY,
    SUM([Lbs.]) OVER(PARTITION BY [Item #],DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[Invoice Date])+1,0))) DOLLARSUM
    FROM #TEMP
)TAB

Now declare the query to get the columns dynamically and to set NULL to Zero

DECLARE @cols NVARCHAR (MAX)
DECLARE @NullToZeroCols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + PIVOTCOL + ']', 
              '[' + PIVOTCOL + ']')
               FROM    (SELECT DISTINCT RNO,PIVOTCOL FROM #NEWTABLE) PV  
               ORDER BY RNO
PRINT @COLS

SET @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+PIVOTCOL+'],0) AS ['+PIVOTCOL+']' 
FROM(SELECT DISTINCT RNO,PIVOTCOL FROM #NEWTABLE GROUP BY RNO,PIVOTCOL)TAB  
ORDER BY RNO  FOR XML PATH('')),2,8000)

Now pivot the query

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT [Item #],' + @NullToZeroCols + ' FROM 
             (
                 SELECT [Item #],VALUE,PIVOTCOL FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 SUM(VALUE)
                 FOR PIVOTCOL IN (' + @cols + ')
            ) p
            ORDER BY [Item #];' 

EXEC SP_EXECUTESQL @query

• SQL FIDDLE

RESULT

Answer 2

In order to get the result you are going to have to either PIVOT twice or UNPIVOT the Dollars and Lbs columns into a single column and then apply the PIVOT once. My preference would be to unpivot and then pivot because I find it to be much easier.

Instead of working dynamically first, you should write the query as a static or hard-coded version to get the logic correct, then convert it to dynamic SQL. The example that I have uses your final dates 201-01-31, etc because you are using a function to create those dates and should be able to apply that as needed.

Since you are using SQL Server 2005+, you can use CROSS APPLY to unpivot Dollars and Lbs. The code will be similar to the following:

select 
  t.ItemNo,
  new_col = convert(varchar(10), t.[invoice date], 120) + '_'+ c.col,
  c.value
from yourtable t
cross apply
(
  select 'Dollars', Dollars union all
  select 'Lbs', Lbs
) c (col, value);

See SQL Fiddle with Demo. This converts your data to the following format:

| ITEMNO |            NEW_COL | VALUE |
|--------|--------------------|-------|
|      A | 2014-01-31_Dollars |     1 |
|      A |     2014-01-31_Lbs |     1 |
|      B | 2014-01-31_Dollars |     2 |
|      B |     2014-01-31_Lbs |     2 |
|      A | 2014-01-31_Dollars |     3 |

I've concatenated into new_col the final column names that you'll need. Again you can format the date in whatever format you need, I just used 2014-01-31 and added the Dollars or Lbs to the end of it. Once you've got the data, you will PIVOT the values into your final desired result:

select ItemNo,
  [2014-01-31_Lbs], [2014-01-31_Dollars],
  [2014-02-28_Lbs], [2014-02-28_Dollars]
from
(
  select 
    t.ItemNo,
    new_col = convert(varchar(10), t.[invoice date], 120) + '_'+ c.col,
    c.value
  from yourtable t
  cross apply
  (
    select 'Dollars', Dollars union all
    select 'Lbs', Lbs
  ) c (col, value)
) d
pivot
(
  sum(value)
  for new_col in ([2014-01-31_Lbs], [2014-01-31_Dollars],
                  [2014-02-28_Lbs], [2014-02-28_Dollars])
) p;

See SQL Fiddle with Demo. Now you've got the result you want, so simply convert it to dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(convert(varchar(10), t.[invoice date], 120) + '_'+ c.col) 
                    from yourtable t
                    cross apply
                    (
                      select 'Lbs', 0 union all
                      select 'Dollars', 1
                    ) c (col, so)
                    group by [invoice date], col, so
                    order by [invoice date], so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ItemNo,' + @cols + ' 
            from 
            (
                select 
                  t.ItemNo,
                  new_col = convert(varchar(10), t.[invoice date], 120) + ''_''+ c.col,
                  c.value
                from yourtable t
                cross apply
                (
                  select ''Dollars'', Dollars union all
                  select ''Lbs'', Lbs
                ) c (col, value)
            ) d
            pivot 
            (
                sum(value)
                for new_col in (' + @cols + ')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. This give a final result of:

| ITEMNO | 2014-01-31_LBS | 2014-01-31_DOLLARS | 2014-02-28_LBS | 2014-02-28_DOLLARS |
|--------|----------------|--------------------|----------------|--------------------|
|      A |              4 |                  4 |              5 |                  5 |
|      B |              6 |                  6 |              6 |                  6 |