SQLTeam.com | Weblogs | Forums

Pivotting/transposing columns to rows

tsql
#1

Hello,

I would like to understand how to move my column values to become rows so that the below table for example:

Original
Original.PNG744×184 3.24 KB

becomes:
Transposed
Transposed.PNG287×595 5.23 KB

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!

#2

hi

i tried to do this !!!!

hope it helps
:slight_smile:
:slight_smile:

i have created my own small sample data
i used UNION ALL instead of case OR pivot

drop create sample data 
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
SQL 
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
Results

image
image.png674×648 7.99 KB

#3

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.

#4

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;
#5

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/

#6

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

#7

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.

#8

Thanks harishgg1, you have reminded me that I did it like this before. A bit long but effective. Thank you!

#9

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!