Search code examples
sqlsql-server-2008pivotunpivot

Transpose Columns or unpivot SQL Query


I am currently attempting to transpose some data inside an SQL query however I can not seem to find a solution using un-pivot. Example of the Data I am working with is

SELECT  * FROM (SELECT 'ACCOUNTS' AS Dept
          , DATENAME(MONTH, GETDATE()) AS [Month]
          , '3254' AS [1st Letter]
          , '2544' AS [2nd Letter]
          , '1254' AS [3rd Letter]
          , '64' AS [4th Letter]
          ) AS t

I will admit I don't fully understand PIVOT and UNPIVOT fully, however I can not seem to work out if it will work in this query? The desired output would be

Dept       |ACCOUNTS
Month      |May
1st Letter |3254
2nd Letter |2544
3rd Letter |1254
4th Letter |64

I have seen a lot of solutions on Google but not really for what I am looking for, would a unpivot do the below for me.


Solution

  • Yes. It just works.

    declare @t table (Dept varchar(20), Month varchar(20), [1st letter]varchar(20),[2nd letter]varchar(20),[3rd letter]varchar(20),[4th letter]varchar(20))
    insert @t 
    SELECT 'ACCOUNTS' AS Dept
              , DATENAME(MONTH, GETDATE()) AS [Month]
              , '3254' AS [1st Letter]
              , '2544' AS [2nd Letter]
              , '1254' AS [3rd Letter]
              , '64' AS [4th Letter]
    
    SELECT  * FROM @t AS t
        unpivot (item for value in (Dept, Month, [1st letter],[2nd letter],[3rd letter],[4th letter])) u