CODEWITHSUNDEEP
sqlconditional-statementsaggregate
Kristian Burghard
Kristian Burghard

Reputation: 1

Aggregate multiple rows to one row, then select which values go to which column SQL

I have a table that looks as follows:

col1 col2 col3 col4 col5 col6 col7
1 ABC ABC1 2904 def
2 ABC ABC2 2400 504 ghi
3 ABC ABC3 504 504 jkl

which needs to be aggregated into one row, looking like this:

col1 col2 col3 col4 col5 col6 col7
1 ABC ABC1 2904 2400 504 def

I understand that it needs to be grouped based on col2. But then what SQL syntax do I need to say that:

Upvotes: 0

Views: 803

Answers (2)

lemon
lemon

Reputation: 15482

Yet another option is using window functions, given that your DBMS allow them, whereas:

  • FIRST_VALUE is in charge of gathering the first value of "col3" by ordering on "col1"
  • LEAD is in charge of gathering the consecutive values of "col5" by ordering on "col1".

Then you retrieve only the first row with the LIMIT 1 clause.

SELECT col1,
       col2,
       FIRST_VALUE(col3) OVER(ORDER BY col1) AS col3,
       col5                                  AS col4,
       LEAD(col5, 1) OVER(ORDER BY col1)     AS col5,
       LEAD(col5, 2) OVER(ORDER BY col1)     AS col6,
       col7
FROM tab
LIMIT 1

Upvotes: 0

avb0101
avb0101

Reputation: 84

Think of this as three different select statements which isolate your min, max and secondary values from Col1. e.g. Select...Where Col1 = 1 (or whatever the min value is), Select...Where Col1 = 2, Select...Where Col1 = max.

Once you have those values broken out you can join all of them together and use the appropriate columns.

I chose to use min, max and row_number in the sub-query because I wasn't sure if your min is always 1 and your secondary value is always 2. If they are, you could write this a bit differently.

select 
    max(Case When a.col1 = mn.MinCol1 Then  a.col1 End) as NewCol1
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col2 End) as NewCol2
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col3 End) as NewCol3
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col5 End) as NewCol4
    ,max(Case When a.col1 = sec.SecondCol1 Then  a.col5 End) as NewCol5
    ,max(Case When a.col1 = mx.MaxCol1 Then  a.col5 End) as NewCol6
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col7 End) as NewCol7
FROM AggTable a
--Get Min Values (Grouped By col2)
LEFT JOIN (Select 
                min(col1) as MinCol1 
                ,col2
          From AggTable
          Group By 
                col2
          ) mn on a.col1 = mn.MinCol1 and a.col2 = mn.col2
--Get Max Values (Grouped By col2)
LEFT JOIN (Select 
                max(col1) as MaxCol1 
                ,col2
            From AggTable
            Group By
                col2
            ) mx on a.col1 = mx.MaxCol1 and a.col2 = mx.col2 
--Get Second Value (Grouped By col2)
LEFT JOIN (select 
            row_number() OVER(Order By col1) as RowNumber
            ,col1  as SecondCol1
            ,col2
          From AggTable
          Group By
                col1
                ,col2
                ) sec on a.col1 = sec.SecondCol1 and a.col2 = sec.col2 and sec.RowNumber = 2

Upvotes: 0

Related Questions