NickName:hc91 Ask DateTime:2015-01-21T21:56:08

SQL Pivot Table dynamic

I've tried so hard to understand how to create a pivot table in SQL, but I can't manage it!

I have the following columns:

link_id   route_section   date_1    StartHour     AvJT    data_source
.......   .............  .......   ...........   ......  ............

With 600,000 rows of data.

I need them in the following pivot table;

date_1 StartHour as column headings
link_id as the row heading
AvJT as the data
with data_source = '1' as the filter.

PIVOT TABLE

Link_ID 
date_1      StartHour    00001a    000002a    000003a    000004a
20/01/2014    8           456       4657        556       46576
21/01/2014    8           511       4725        601       52154
22/01/2014    8           468       4587        458       47585
23/01/2014    8           456       4657        556       46576
24/01/2014    8           456       4657        556       46576
25/01/2014    8           456       4657        556       46576
26/01/2014    8           456       4657        556       46576

I've managed to get the following code, this works but only gives me date_1 as column heading and not StartHour additionally, or with the filter as date_source = '1'.

    Use [C1_20132014]

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Link_ID)
FROM (SELECT DISTINCT Link_ID FROM C1_May_Routes) AS Link_ID

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Date_1, ' + @ColumnName + '
    FROM C1_May_Routes
    PIVOT(SUM(AvJT) 
          FOR Link_ID IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Thanks for any help,

Henry

Copyright Notice：Content Author:「hc91」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/28068971/sql-pivot-table-dynamic

Answers

Sarath Subramanian 2015-01-21T14:16:40

Here you will select the values in a column to show as column in pivot\n\nDECLARE @cols NVARCHAR (MAX)\n\nSELECT @cols = COALESCE (@cols + ',[' + AvJT + ']', '[' + AvJT + ']')\n FROM (SELECT DISTINCT AvJT FROM YourTable) PV \n ORDER BY AvJT\n\n\nNow pivot the query\n\nDECLARE @query NVARCHAR(MAX)\nSET @query = 'SELECT * FROM \n (\n SELECT date_1, StartHour,AvJT, data_source \n FROM YourTable\n ) x\n PIVOT \n (\n -- Values in each dynamic column\n SUM(data_source)\n FOR AvJT IN (' + @cols + ') \n ) p;' \n\nEXEC SP_EXECUTESQL @query\n\n\n\nClick here to view result\n\n\nIf you want to do it to where column names are not dynamic, you can do the below query\n\nSELECT DATE_1,STARTHOUR,\nMIN(CASE WHEN AvJT='00001a' THEN data_source END) [00001a],\nMIN(CASE WHEN AvJT='00002a' THEN data_source END) [00002a],\nMIN(CASE WHEN AvJT='00003a' THEN data_source END) [00003a],\nMIN(CASE WHEN AvJT='00004a' THEN data_source END) [00004a]\nFROM YOURTABLE\nGROUP BY DATE_1,STARTHOUR\n\n\n\nClick here to view result\n\n\nEDIT : \n\nI am updating for your updated question.\n\nDeclare a variable for filtering data_source\n\nDECLARE @DATASOURCE VARCHAR(20) = '1' \n\n\nInstead of QUOTENAME, you can use another format to get the columns for pivot\n\nDECLARE @cols NVARCHAR (MAX)\n\nSELECT @cols = COALESCE (@cols + ',[' + Link_ID + ']', '[' + Link_ID + ']')\n FROM (SELECT DISTINCT Link_ID FROM C1_May_Routes WHERE data_source=@DATASOURCE) PV \n ORDER BY Link_ID\n\n\nNow pivot \n\nDECLARE @query NVARCHAR(MAX)\nSET @query = 'SELECT * FROM \n (\n -- We will select the data that has to be shown for pivoting\n -- with filtered data_source\n SELECT date_1, StartHour,AvJT, Link_ID\n FROM C1_May_Routes\n WHERE data_source = '+@DATASOURCE+'\n ) x\n PIVOT \n (\n -- Values in each dynamic column\n SUM(AvJT)\n -- Select columns from @cols \n FOR Link_ID IN (' + @cols + ') \n ) p;' \n\nEXEC SP_EXECUTESQL @query\n\n\n\nClick here to view result\n",