Unpivot

Transact-SQL
1

I have been playing around with UNPIVOT but it may not be the correct usage. Basically, is it possible to keep the field name and turn it into data? I thought a sample was shown to me in one of my other questions but I believe that was PIVOT.

Here is an example of the table:

CustomerID Sunday Monday Tuesday Wednesday Thursday Friday Saturday
111 1 2 3 4 5 6 7

Here is what I am trying to achieve:

CustomerID Day Unit
111 Sunday 1
111 Monday 2
111 Tuesday 3
111 Wednesday 4
111 Thursday 5
111 Friday 6
111 Saturday 7
2 
if OBJECT_ID('tempDb..#temp') is not null drop table #temp

create table #temp
(
customerId int,
Sunday numeric(4,0),
Monday numeric(4,0),
Tuesday numeric(4,0),
Wednesday numeric(4,0),
Thursday numeric(4,0),
Friday numeric(4,0),
Saturday numeric(4,0)
)

insert #temp 
values
('111','1','2','3','4','5','6','7');


select p.customerId 
,p.[weekday]
,p.num 
from #temp 
unpivot
(
[num] for [weekday] in
(
Sunday,	Monday,	Tuesday,	Wednesday,	Thursday,	Friday,	Saturday

)
) p

go

drop table #temp
1 Like
3

Thank you Femiolan for showing the proper use of UNPIVOT.

BTW... I saw ScottPletcher used a CROSS APPLY method from a thread called "Trying to unpivot/pivot columns in temp table". What is the difference between using UNPIVOT and CROSS APPLY in this case?

SELECT * FROM #temp

SELECT aT1.* FROM #temp
CROSS APPLY 
(
    VALUES
			(Customer_ID, 'Sunday', Sunday),
			(Customer_ID, 'Monday', Monday),
			(Customer_ID, 'Tuesday', Tuesday),
			(Customer_ID, 'Wednesday', Wednesday),
			(Customer_ID, 'Thursday', Thursday),
			(Customer_ID, 'Friday', Friday),
			(Customer_ID, 'Saturday', Saturday)
) AS aT1 (New_Customer_ID, New_Day, My_Value)

Also thanks AndyC. I just realized that you also showed how to do an Unpivot example in my thread "Combining Fields" I should have checked that thread before posting this one.

1 Like
4

Very little. It really comes down to what happens with NULL values. If you added a WHERE My_Value Is Not Null then they are basically equivalent.

1 Like
5

Hi Andy,

I was wondering. Since CROSS APPLY is another method to UNPIVOT data, can it be used to PIVOT data?

6

Manually PIVOTing usually comes down to a combination of an aggregate function (SUM, MAX etc) around a CASE statement, coupled with a GROUPing. It's a bit more advanced to figure out but can be significantly more efficient in cases where your need to pivot based on multiple columns.