restructuring sql rows into columns of the same type

Question

This is my first time using a forum like this for SQL assistance so bear with me! I have a large query set for student data, each student has one row where the primary join table is their application record (all other tables joined onto that). Now, I'm looking to add in degree data, however many students have multiple degrees so it's duplicating the student rows (boo!).

Here is a shortened version of what it looks like:

StudentID CohortTerm    DegreeYear  DegreeMonth  
1         2009 Spring   2011        05            
1         2009 Spring   2013        10            
2         2012 Fall     2015        05            
3         2015 Fall     2017        05            
3         2015 Fall     2018        05

Here is what I want it to look like:

StudentID CohortTerm  Degree1_Y  Degree1_M  Degree2_Y  Degree2_M 
1         2009 Spring   2011        05      2013       10   
2         2012 Fall     2015        05            
3         2015 Fall     2017        05      2018       05

I have looked up PIVOT, but I don't want the count of the degrees, I want their literal values to be displayed just creating columns to signify the first degree, the second degree, and so on. — G.BOWLER, Apr 25 '18 at 17:18

score 1 · Answer 1 · answered Apr 26 '18 at 13:01

And here's another way doing this with analytical functions:

WITh test_data AS
(
 SELECT 1 StudentID, 2009 Cohort, 'Spring' Term, 2011 DegreeYear, '05' DegreeMonth FROM dual
  UNION ALL
 SELECT 1, 2009, 'Spring', 2013, '10' FROM dual    
  UNION ALL
 SELECT 2, 2012, 'Fall'  , 2015, '05' FROM dual    
  UNION ALL
 SELECT 3, 2015, 'Fall'  , 2017, '05' FROM dual     
  UNION ALL
 SELECT 3, 2015, 'Fall'  , 2018, '05' FROM dual         
 ) 
SELECT * FROM
 (
 SELECT ROW_NUMBER() OVER (PARTITION BY Cohort ORDER BY Cohort, DegreeYear, DegreeMonth) rno
 , StudentID, Cohort, Term, DegreeYear, DegreeMonth 
 , LEAD(DegreeYear)  OVER (PARTITION BY Cohort ORDER BY Cohort, DegreeYear, DegreeMonth) DegreeYear2
 , LEAD(DegreeMonth) OVER (PARTITION BY Cohort ORDER BY Cohort, DegreeYear, DegreeMonth) DegreeMonth2
  FROM test_data
)
WHERE rno = 1
ORDER BY Cohort, DegreeYear, DegreeMonth
/

Output:

RNO STUDENTID   COHORT  TERM    DEGREEYEAR  DEGREEMONTH DEGREEYEAR2 DEGREEMONTH2
1         1      2009   Spring      2011         05         2013        10
1         2      2012   Fall        2015         05     
1         3      2015   Fall        2017         05          2018       05

score 0 · Answer 2 · answered Apr 25 '18 at 18:08

0

As others have commented, pivot is what you are looking for. See pivot docs here.

answered Apr 25 '18 at 18:08

RJ7

883
9
17

What function would I use in lieu of count? – G.BOWLER May 01 '18 at 19:55
Where do you need to use count in the example you provided? – RJ7 May 02 '18 at 13:33
In the Pivot docs link provided [aggregate function] which is usually COUNT in my understanding. What aggregate function would you use in this scenario? – G.BOWLER May 02 '18 at 20:52
Take a look at https://stackoverflow.com/questions/1663505/denormalizing-data-maybe-a-pivot. This is basically the same scenario you need to mimic. – RJ7 May 03 '18 at 13:54

restructuring sql rows into columns of the same type

2 Answers2