CODEWITHSUNDEEP
sqlsql-server-2008pivotunpivot
user3615823
user3615823

Reputation:

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.

Upvotes: 3

Views: 188

Answers (2)

MatBailie
MatBailie

Reputation: 86735

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).

Upvotes: 0

podiluska
podiluska

Reputation: 51494

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

Upvotes: 2

Related Questions