convert columns to rows using pivot

Question

I want to convert cols to rows to fit the format in the image This is my query:

Select dt1.Year,dt1.oneyearret as '1-Year Retention',dt2.twoyearret as '2-Year Retention',dt4.fouryeargrad as '4-Year Graduation Rate',dt5.fiveyeargrad as '5-Year Graduation Rate',dt6.sixyeargrad as '6-Year Graduation Rate' from
(select case WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2015' then '2015' WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2016' then '2016' else (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)))  end  as 'Year',
                                  cast(convert(varchar,(Convert(Money,SUM(rg.RET1_FF),1)/Convert(Money,SUM(rg.COHORT_COUNT),1))*100,1) as numeric(10,1)) as oneyearret         
                                  from vw_Retention_Graduation_F rg
                                  inner join dim_Time t
                                  on rg.TIME_KEY = t.TIME_KEY
                                  inner join dim_Part_Full pf
                                  on rg.PART_FULL_KEY=pf.PART_FULL_KEY
                                  inner join dim_Student_Level sl
                                  on rg.STUDENT_LEVEL_KEY=SL.STUDENT_LEVEL_KEY
                                  inner join dim_Student_Population sp
                                  on rg.STUDENT_POPULATION_KEY=sp.STUDENT_POPULATION_KEY
                                  inner join dim_College c
                                  on rg.COLLEGE_KEY = c.COLLEGE_KEY
                                  WHERE t.ACADEMIC_PERIOD between 201527 and 201627
                                  AND pf.PART_FULL = 'F'
                                  AND sl.STUDENT_LEVEL = 'UG'
                                  AND sp.STUDENT_POPULATION = 'N'
                                  GROUP BY (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)) ) ) dt1 inner join
(select case WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2014' then '2015' WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2015' then '2016' else (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)))  end  as 'Year',
                                  cast(convert(varchar,(Convert(Money,SUM(rg.RET2_FF),1)/Convert(Money,SUM(rg.COHORT_COUNT),1))*100,1) as numeric(10,1)) as twoyearret         
                                  from vw_Retention_Graduation_F rg
                                  inner join dim_Time t
                                  on rg.TIME_KEY = t.TIME_KEY
                                  inner join dim_Part_Full pf
                                  on rg.PART_FULL_KEY=pf.PART_FULL_KEY
                                  inner join dim_Student_Level sl
                                  on rg.STUDENT_LEVEL_KEY=SL.STUDENT_LEVEL_KEY
                                  inner join dim_Student_Population sp
                                  on rg.STUDENT_POPULATION_KEY=sp.STUDENT_POPULATION_KEY
                                  inner join dim_College c
                                  on rg.COLLEGE_KEY = c.COLLEGE_KEY
                                  WHERE t.ACADEMIC_PERIOD between 201427 and 201527
                                  AND pf.PART_FULL = 'F'
                                  AND sl.STUDENT_LEVEL = 'UG'
                                  AND sp.STUDENT_POPULATION = 'N'
                                  GROUP BY (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)) ) ) dt2 on dt1.Year = dt2.year
                                  inner join
(select case WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2012' then '2015' WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2013' then '2016' else (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)))  end  as 'Year',
                                  cast(convert(varchar,(Convert(Money,SUM(rg.GRAD4_FF),1)/Convert(Money,SUM(rg.COHORT_COUNT),1))*100,1) as numeric(10,1)) as fouryeargrad         
                                  from vw_Retention_Graduation_F rg
                                  inner join dim_Time t
                                  on rg.TIME_KEY = t.TIME_KEY
                                  inner join dim_Part_Full pf
                                  on rg.PART_FULL_KEY=pf.PART_FULL_KEY
                                  inner join dim_Student_Level sl
                                  on rg.STUDENT_LEVEL_KEY=SL.STUDENT_LEVEL_KEY
                                  inner join dim_Student_Population sp
                                  on rg.STUDENT_POPULATION_KEY=sp.STUDENT_POPULATION_KEY
                                  inner join dim_College c
                                  on rg.COLLEGE_KEY = c.COLLEGE_KEY
                                  WHERE t.ACADEMIC_PERIOD between 201227 and 201327
                                  AND pf.PART_FULL = 'F'
                                  AND sl.STUDENT_LEVEL = 'UG'
                                  AND sp.STUDENT_POPULATION = 'N'
                                  GROUP BY (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)) ) ) dt4 on dt2.Year = dt4.year
                                  inner join
(select case WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2011' then '2015' WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2012' then '2016' else (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)))  end  as 'Year',
                                  cast(convert(varchar,(Convert(Money,SUM(rg.GRAD5_FF),1)/Convert(Money,SUM(rg.COHORT_COUNT),1))*100,1) as numeric(10,1)) as fiveyeargrad        
                                  from vw_Retention_Graduation_F rg
                                  inner join dim_Time t
                                  on rg.TIME_KEY = t.TIME_KEY
                                  inner join dim_Part_Full pf
                                  on rg.PART_FULL_KEY=pf.PART_FULL_KEY
                                  inner join dim_Student_Level sl
                                  on rg.STUDENT_LEVEL_KEY=SL.STUDENT_LEVEL_KEY
                                  inner join dim_Student_Population sp
                                  on rg.STUDENT_POPULATION_KEY=sp.STUDENT_POPULATION_KEY
                                  inner join dim_College c
                                  on rg.COLLEGE_KEY = c.COLLEGE_KEY
                                  WHERE t.ACADEMIC_PERIOD between 201127 and 201227
                                  AND pf.PART_FULL = 'F'
                                  AND sl.STUDENT_LEVEL = 'UG'
                                  AND sp.STUDENT_POPULATION = 'N'
                                  GROUP BY (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)) ) ) dt5 on dt4.Year = dt5.year
                                  inner join
(select case WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2010' then '2015' WHEN (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4))) ='2011' then '2016' else (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)))  end  as 'Year',
                                  cast(convert(varchar,(Convert(Money,SUM(rg.GRAD6_FF),1)/Convert(Money,SUM(rg.COHORT_COUNT),1))*100,1) as numeric(10,1)) as sixyeargrad         
                                  from vw_Retention_Graduation_F rg
                                  inner join dim_Time t
                                  on rg.TIME_KEY = t.TIME_KEY
                                  inner join dim_Part_Full pf
                                  on rg.PART_FULL_KEY=pf.PART_FULL_KEY
                                  inner join dim_Student_Level sl
                                  on rg.STUDENT_LEVEL_KEY=SL.STUDENT_LEVEL_KEY
                                  inner join dim_Student_Population sp
                                  on rg.STUDENT_POPULATION_KEY=sp.STUDENT_POPULATION_KEY
                                  inner join dim_College c
                                  on rg.COLLEGE_KEY = c.COLLEGE_KEY
                                  WHERE t.ACADEMIC_PERIOD between 201027 and 201127
                                  AND pf.PART_FULL = 'F'
                                  AND sl.STUDENT_LEVEL = 'UG'
                                  AND sp.STUDENT_POPULATION = 'N'
                                  GROUP BY (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD_all,1,4)) ) )  dt6 on dt5.Year = dt6.year

Current Output:

Required Output:

Wow that query is seriously ugly. Some formatting would turn that wall of text into something that doesn't make others want to kill themselves. — Sean Lange, Jul 25 '17 at 15:07
So I help you format your post and you tell me to not bother. That is not a great attitude to present to people who are trying to help you. — Sean Lange, Jul 25 '17 at 15:15
Maybe this is of some help to you https://stackoverflow.com/questions/15297809/sql-transpose-full-table ? — Carsten Massmann, Jul 25 '17 at 15:50
Oh I have a feeling not many will bother, especially those of the likes of @SeanLange and I. — S3S, Jul 25 '17 at 15:57

Carsten Massmann · Answer 1 · 2017-07-25T16:06:48.633

1

After replacing your lengthy SQL code with a shorter one, simply producing your sample data, the actual "transpose" action can be achieved with something like this:

create table tbl (yr int, y1ret float, y2ret float, y4grad float, y5grad float, y6grad float);
insert into tbl VALUES (2015, 83.5, 71.4, 34.8, 54.7, 59.9 ), (2016, 83.6, 73.8, 33.8, 53.6, 60);

select * from (
  select yr, col, value
  from tbl
  unpivot
  (
    value
    for col in (y1ret, y2ret, y4grad, y5grad, y6grad)
  ) unpiv

) src
pivot
(
  max(value)
  for yr in ([2015], [2016])
) piv

This is of course an adaptation of the original solution presented here: SQL transpose full table
A running demo can be found here: http://rextester.com/CNYR30557

In most applications it is simpler and faster to do this kind of manipulation in the receiving environment, i.e. either in the back-end using PHP or in the front-end using JavaScript.

Edit:

To accommodate your lengthy SQL code into this you could do:

;WITH tbl AS ( -- YOUR CODE GOES HERE --
)
select * from (
  select yr, col, value
  from tbl
  unpivot
  (
    value
    for col in (y1ret, y2ret, y4grad, y5grad, y6grad)
  ) unpiv

) src
pivot
(
  max(value)
  for yr in ([2015], [2016])
) piv

edited Jul 25 '17 at 16:06

answered Jul 25 '17 at 15:58

Carsten Massmann

26,510
2
22
43

Thanks for this, but i dont have permission to create a table. – Gaurav Vinayaka Jul 25 '17 at 16:02
You dont need to create the table. That was merely a placeholder. If you put your code into a "Common Table Expression" then you can run the whole thing from there. A "CTE" behaves just like a view or a table. – Carsten Massmann Jul 25 '17 at 16:04
Ahh gotit! thanks a ton! – Gaurav Vinayaka Jul 25 '17 at 16:05
what does col and value represent here? – Gaurav Vinayaka Jul 25 '17 at 16:19
They are names you can choose for yourself and are part of the `unpivot` clause. You need to generate this intermediate format first before you can stitch it together again with `pivot`, see here https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx or here: https://stackoverflow.com/questions/19055902/unpivot-with-column-name – Carsten Massmann Jul 25 '17 at 16:28
i should create the names col yr and value before in my query and then use them in unpivot? – Gaurav Vinayaka Jul 25 '17 at 16:46
i got the output! thanks a lot!! – Gaurav Vinayaka Jul 25 '17 at 17:20

convert columns to rows using pivot

1 Answers1