SQL Transpose Columns to Rows with fluctuating Rows

Question

I am attempting to Transpose the columns and rows on a view, however the rows are dates that change weekly which I cannot seem to get functioning correctly.

What I have is the below;

Date       | Report1 | Report2 |
---------- | ------- | ------- | 
2017-07-01 | N/A     | Yes     |
2017-07-02 | Yes     | Yes     |
2017-07-03 | N/A     | Yes     |
2017-07-04 | Yes     | Yes     |
2017-07-05 | N/A     | Yes     |
2017-07-06 | NULL    | NULL    |
2017-07-07 | N/A     | N/A     |

and what I want it to look like is;

Date     | 2017-07-01 | 2017-07-02 | 2017-07-03 | 2017-07-04 | 2017-07-05 | 2017-07-06 | 2017-07-07 |
-------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- |
Report1  | N/A        | Yes        | N/A        | Yes        | N/A        | Null       | N/A        |
Report2  | Yes        | Yes        | Yes        | Yes        | Yes        | Null       | N/A        |

The only issue is that the Dates are rolling so the column names will change. *There are also a lot more reports but for simplicity 2 will suffice"

I would suggest you either handle this in the display layer (SSRS matrix for example) or make a procedure for a [dynamic sql approach](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — JohnHC, Jul 13 '17 at 12:56
I've done this using dynamics SQL and pivot. Write a query to generate the last 7 days dates and then use that query as your pivot column list. — Jacob H, Jul 13 '17 at 13:03

Nenad Zivkovic · Answer 1 · 2017-07-13T15:08:31.453

You actually need a combination of UNPIVOT and Dynamic PIVOT

IF (OBJECT_ID('tempdb..#unpvt') IS NOT NULL) DROP TABLE #unpvt;
SELECT Report, Val, [Date]
INTO #unpvt
FROM 
(
    SELECT [Date], [Report1], [Report2]
    FROM YourTable
) p
UNPIVOT
   (Val FOR Report IN ([Report1], [Report2])
)AS unpvt;


DECLARE @cols AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ', ' + QUOTENAME([Date])
                   FROM YourTable c
                   FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'');

SET @sql = 'SELECT Report, ' + @cols + ' FROM
            (
                SELECT * FROM #unpvt
            ) x
            PIVOT 
            (
                MAX(Val)
                FOR [Date] in (' + @cols + ')
            ) p ';


EXECUTE(@sql);

score 0 · Accepted Answer · answered Jul 13 '17 at 13:42

The following will dynamically unpivot your data and then create/execute a dynamic pivot.

With a dynamic UNPIVOT, there is not need to specify the fields (or "reports")

Also, with the date parameters (@Date1 and @Date2), you can specify the desired date range to pivot

Example

Declare @Date1 varchar(10) = '2017-07-01'
Declare @Date2 varchar(10) = '2017-07-07'

Declare @SQL varchar(max) = '
Declare @XML xml = (Select * from YourTable Where Date Between '''+@Date1+''' and '''+@Date2+''' for XML RAW)

Select *
 From (
        Select Date   = r.value(''@Date'',''Date'')
              ,Item   = attr.value(''local-name(.)'',''varchar(100)'')
              ,Value  = attr.value(''.'',''varchar(max)'') 
         From  @XML.nodes(''/row'') as A(r)
         Cross Apply A.r.nodes(''./@*'') AS B(attr)
         Where attr.value(''local-name(.)'',''varchar(100)'') not in (''Date'')
      ) A
 Pivot (max([Value]) For [Date] in (' + Stuff((Select Distinct ','+QuoteName(Date) 
                                               From  YourTable
                                               Where Date Between @Date1 and @Date2
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'
Exec(@SQL);
--Print @SQL

Returns

SQL Transpose Columns to Rows with fluctuating Rows

2 Answers2