0

I have a large data set and am trying to replicate an excel pivot table in SQL Server. I am unable to find an example where I do not have to manually name the column headings using the PIVOT function in T-SQL.

I am looking for a way to use SELECT DISTINCT [column 1] as my row names and SELECT DISTINCT [COLUMN 2] as my column names and populate that table using data in a third column.

Please help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jordan
  • 11
  • 2
  • 1
    The only way to do this in SQL Server is to use dynamic sql, which can be a bit messy and difficult to debug. – Nick Feb 14 '16 at 05:16
  • Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – TT. Feb 14 '16 at 09:17
  • 1
    Many examples on SO, here's more: [example1](https://stackoverflow.com/questions/12430512/dynamic-pivot-table-in-sql-server); [example2](https://stackoverflow.com/questions/35347342/sql-dynamic-pivot-for-goals/35350343#35350343), [example3](https://stackoverflow.com/questions/35192602/pivot-and-unpivot-for-4-joined-table-sql-server/35204649#35204649), [example4](https://stackoverflow.com/questions/35173463/sql-pivot-insertion/35175883#35175883) and so on. – TT. Feb 14 '16 at 09:21

1 Answers1

0

Try This:

    DECLARE @v_Columns VARCHAR(MAX),@v_StartDate DATETIME = '09/15/2015',@v_EndDate DATETIME = '12/15/2020',@v_Query VARCHAR(MAX)SELECT @v_Columns = COALESCE(@v_Columns,'[') + convert(varchar, Date, 101) + '],[' 
FROM 
(SELECT DISTINCT Date FROM view_wc_sessions_info) th
WHERE
th.Date BETWEEN @v_StartDate AND @v_EndDate

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

SET @v_Query = 

'SELECT
*
FROM
(
 select [Total], Date, fk_student_rcid
  from view_WC_SESSIONS_Info th
WHERE
th.Date BETWEEN ''' + CONVERT(VARCHAR(50), @v_StartDate, 101) + '''
AND ''' + CONVERT(VARCHAR(50), @v_EndDate, 101) + '''
) src
PIVOT
(
COUNT(src.[Total])
FOR src.Date IN (' + @v_Columns + ')
) AS pivotview'

EXEC(@v_Query)
mohan111
  • 8,633
  • 4
  • 28
  • 55
GabrielVa
  • 2,353
  • 9
  • 37
  • 59
  • ^ I think that code works but im getting the following error Msg 1701, Level 16, State 1, Line 1 Creating or altering table 'FakeWorkTable' failed because the minimum row size would be 8205, including 133 bytes of internal overhead. This exceeds the maximum allowable table row size of 8094 bytes. Msg 8630, Level 17, State 48, Line 1 Internal Query Processor Error: The query processor encountered an unexpected error during execution (HRESULT = 0x80004005). – Jordan Feb 16 '16 at 06:06