CODEWITHSUNDEEP
sqlpostgresql
Jake Wagner
Jake Wagner

Reputation: 826

combine values from multiple rows into single

I have a table which stores student admission details and the admission date. Admission Date is bifurcated into multiple rows and I would like to combine the values from the three rows into a single row.

select * from table

student_name         student_value         student_answer
   Drake                 day                    28
   Drake                 month                  2
   Drake                 year                  2010
   Drake                city                  San Diego
   Drake                zip code                11235
   Josh                  day                    15
   Josh                  month                   5
   Josh                  year                  2012
   Josh                 city                  Sacramento
   Josh                 zip code                45876

Result Desired (new_column):

student_name         student_value         student_answer         new_column
   Drake                 day                   28                  9282010
   Drake                 month                 9                   9282010
   Drake                 year                 2010                 9282010
   Drake                city                 San Diego             San Diego
   Drake                zip code               11235                11235
   Josh                  day                   15                  11152012
   Josh                  month                 11                  11152012
   Josh                  year                  2012                11152012
   Josh                 city                  Sacramento           Sacramento
   Josh                 zip code                45876                45876

Upvotes: 1

Views: 209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You can use conditional window functions:

select t.*,
       (case when student_value in ('day', 'month', 'year')
             then max(student_answer) filter (where student_value = 'month') over (partition by student_name) ||
                  max(student_answer) filter (where student_value = 'day') over (partition by student_name) ||
                  max(student_answer) filter (where student_value = 'year') over (partition by student_name)
             else student_answer
        end) as new_column         
from t;

Upvotes: 3

Related Questions