Hello,
I would like to understand how to move my column values to become rows so that the below table for example:
I would like to do this without using the PIVOT/UNPIVOT functions. In the past, I have used case when but this is when making the rows become columns and this way around is confusing me!
Any help much appreciated, thanks!
hi
i tried to do this !!!!
hope it helps
i have created my own small sample data
i used UNION ALL instead of case OR pivot
use tempdb
go
drop table data
go
create table data
(
id int ,
cohort int,
year0 int ,
year1 int ,
year2 int
)
go
insert into data select 1,2011,0,0,0
insert into data select 2,2010,0,0,1
insert into data select 3,2011,0,1,1
go
select ID,cohort,'year0' as year ,year0 as value from data
union all
select ID,cohort,'year1',year1 from data
union all
select ID,cohort,'year2',year2 from data
go
I guess my question would be, why do you have duplicate data for the combination of Cohort and Year? Better than that, how/why can the combination of Cohort=2011 and Year='Year0' have two values of 0 and 1?
{edit} Never mind... it would appear that "ID" is being used for something other than just a meaningless sequence here.
Here is an option without using UNPIVOT:
Declare @pivotedData Table (id int, cohort int, year0 int, year1 int, year2 int, year3 int, year4 int, year5 int);
Insert Into @pivotedData (id, cohort, year0, year1, year2, year3, year4, year5)
Values (1, 2010, 0, 0, 0, 1, 0, 0)
, (2, 2011, 0, 0, 0, 0, 1, 0)
, (3, 2010, 1, 0, 0, 0, 1, 0)
, (4, 2011, 0, 1, 0, 0, 1, 0)
, (5, 2010, 0, 0, 1, 0, 1, 0)
, (6, 2011, 0, 0, 0, 0, 1, 0)
, (7, 2010, 0, 0, 1, 0, 1, 0);
Select id
, pd.cohort
, y.yearValue
From @pivotedData pd
Cross Apply (Values (pd.year0)
, (pd.year1)
, (pd.year2)
, (pd.year3)
, (pd.year4)
, (pd.year5)
) As y(yearValue);
I am not sure why you would not use UNPIVOT though:
Select upvt.id
, upvt.cohort
, upvt.yearValue
From @pivotedData pd
Unpivot (yearValue For yearCol In (year0, year1, year2, year3, year4, year5)) upvt;
First, nice job on both examples. To answer your implied question above...
There are actually several reasons that I prefer the CROSS APPLY/VALUES method of unpivoting compared to the UNPIVOT operator. See the following article that a good friend (who, unfortunately, is no longer among the living... Rest in Peace, ol' friend) of mine wrote.
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
I prefer the CROSS APPLY approach myself - but for simple cases UNPIVOT works. When I see someone asking for an approach because they can't use something I would like to know why...
Absolutely understood, Jeff. It was, in no way, a slam on you. I just wanted to make sure people understood that there actually is a reason to not use UNPIVOT.
Thanks harishgg1, you have reminded me that I did it like this before. A bit long but effective. Thank you!
Thanks Jeff, I have to say I didn't think unpivot was available in my version of SQL Server. However, I have just tried this and I am so happy that I have learnt something new. Thank you!