CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Joyиal Ali
Joyиal Ali

Reputation: 89

Pivot table in SQL (column results to rows)

how do I pivot a results query?

currently it looks like this

Hours | Volume
8     | 1000
9     | 1012 
10    | 1045
11    | 1598
12    | 1145
13    | 1147

into this

8   |9   |10  |11  |12  |13
1000|1012|1045|1598|1145|1147

I tried the following but its not working

Select #TEMP.volume,
       pvt.volume,
          #TEMP.volume
          pvt.volume
FROM #TEMP
PIVOT (volume FOR [DialHour] IN ( "8", "9", "10", "11","12","13","14","15","16","17","18","19")) AS pvt

Upvotes: 0

Views: 13062

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

This assumes you are working with SQL Server , then it is always better to use dynamic PIVOT approach 

declare @col nvarchar(max), @query nvarchar(max)

select @col = stuff(
                      (select ','+quotename(Hours) from #tm for xml path('')),
                       1,1, '')

set @query =   N'select * from
                (
                  select * from #tm
                )a 
                PIVOT
                (
                  MAX(Volume) for Hours in ('+@col+')
                )pvt'

EXECUTE sp_executesql @query

Result :

8       9       10      11      12      13
1000    1012    1045    1598    1145    1147

Upvotes: 1

Andrew
Andrew

Reputation: 373

I am creating this table in T-SQL for demonstration purposes.

CREATE TABLE #TEMP (HOURS INT, VOLUME INT)
INSERT INTO #TEMP VALUES
(8 ,1000),
(9 ,1012), 
(10,1045),
(11,1598),
(12,1145),
(13,1147)
;

If you are not using T-SQL (SQL Server), you could run into issues with a Pivot example. The syntax is different in different platforms. Here is a more standard approach.

SELECT
 MAX(CASE WHEN HOURS = 8  THEN VOLUME END) AS [8]
,MAX(CASE WHEN HOURS = 9  THEN VOLUME END) AS [9]
,MAX(CASE WHEN HOURS = 10 THEN VOLUME END) AS [10]
,MAX(CASE WHEN HOURS = 11 THEN VOLUME END) AS [11]
,MAX(CASE WHEN HOURS = 12 THEN VOLUME END) AS [12]
,MAX(CASE WHEN HOURS = 13 THEN VOLUME END) AS [13]
FROM #TEMP
;

It is better to label integers/numbers with an alphabet character instead of relying on brackets. This approach should work on any platform.

SELECT
 MAX(CASE WHEN HOURS = 8  THEN VOLUME END) AS COL_8
,MAX(CASE WHEN HOURS = 9  THEN VOLUME END) AS COL_9
,MAX(CASE WHEN HOURS = 10 THEN VOLUME END) AS COL_10
,MAX(CASE WHEN HOURS = 11 THEN VOLUME END) AS COL_11
,MAX(CASE WHEN HOURS = 12 THEN VOLUME END) AS COL_12
,MAX(CASE WHEN HOURS = 13 THEN VOLUME END) AS COL_13
FROM #TEMP
;

Upvotes: 1

Karel-Jan Misseghers
Karel-Jan Misseghers

Reputation: 807

You can find a clear example on technet; https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Your code should look more like the below;

Select volume, [8], [9], [10], [11],[12],[13],[14],[15],[16],[17],[18],[19]
FROM 
(SELECT volume FROM #TEMP) AS SourceTable
PIVOT
(
hours 
FOR volume IN ("8", "9", "10", "11","12","13","14","15","16","17","18","19")
) AS PivotTable;

Upvotes: 1

Ven
Ven

Reputation: 2014

It is always good to use pivot query in addition to an ID column , to simplify u can get result set this way 

WITH cte
AS (
    SELECT *
    FROM #TEMP
    PIVOT(max(volume) FOR [DialHour] IN (
                "8"
                ,"9"
                ,"10"
                ,"11"
                ,"12"
                ,"13"
                ,"14"
                ,"15"
                ,"16"
                ,"17"
                ,"18"
                ,"19"
                )) AS pvt
    )
SELECT 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19...
FROM cte

Upvotes: 0

Related Questions