CODEWITHSUNDEEP
sqloracle-database
G.BOWLER
G.BOWLER

Reputation: 11

restructuring sql rows into columns of the same type

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

Upvotes: 1

Views: 133

Answers (2)

Art
Art

Reputation: 5782

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

Upvotes: 1

RJ7
RJ7

Reputation: 923

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

Upvotes: 0

Related Questions