Transpose Columns or unpivot SQL Query

Question

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.

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=UNPIVOTData — MatBailie, May 09 '14 at 09:42
You want to Unpivot, so that you got one column for each letter, am I right? Is there a fixed number of letters in your table? — Gunnar Sjúrðarson Knudsen, May 09 '14 at 09:44

score 2 · Accepted Answer · answered May 09 '14 at 09:46

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

ahhh, so in a pivot the "item for Value in" specifies the Row names for the pivot.I never understood that. Perfect thank you! — , May 09 '14 at 09:54

score 0 · Answer 2 · answered May 09 '14 at 09:48

SELECT
  unpvt.[key],
  unpvt.[value]
FROM
  t
UNPIVOT
(
  [key] FOR [value] IN ([Dept],[Month],[1st letter],[2nd letter],[3rd letter],[4th letter])
)
  AS unpvt

The UNPIVOT effectively joins on a new table of two columns. In the case the [key] and the [value].

[key] is the string representation of the field name.

[value] is the value that was stored in that field.

The IN list allows you to specify which fields are being pivoted.

NOTE: This does mean that you need to know the full list of field names in advance; it won't dynamically adjust to include more fields if you add them to the table. Also, take care when your fields have different data-types (though this is not the case in your example).

Transpose Columns or unpivot SQL Query

2 Answers2