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