CODEWITHSUNDEEP
sqlsql-server-2008common-table-expression
Ali
Ali

Reputation: 85

Spliting GROUP BY results into different columns

I have a column containing date ranges and the number of days passed associated to a specific ID (one to many), based on the number of records associated to it, I want those results split into columns instead of individual rows, so from this:

id_hr  dd beg          end
----------------------------------------
1      10 05/01/2019   15/01/2019
1      5  03/02/2019   08/02/2019
2      8  07/03/2019   15/03/2019

Could become this:

id_hr  dd beg          end        dd beg          end
--------------------------------- ---------------------
1      10 05/01/2019   15/01/2019 5  03/02/2019   08/02/2019
2      8  07/03/2019   15/03/2019

I did the same in a worksheet (pivot table) but the table became as slow as it could get, so I'm looking for a more friendly approach in SQL, I did a CTE which number the associated rows and then select each one and display them in new columns.

;WITH CTE AS(
   SELECT PER_PRO, ID_HR, NOM_INC, rut_dv, dias_dur, INI, FIN,
       ROW_NUMBER()OVER(PARTITION BY ID_HR ORDER BY SUBIDO) AS RN
   FROM dbo.inf_vac WHERE PER_PRO = 201902
)
SELECT ID_HR, NOM_INC, rut_dv,
    (case when rn = 1 then DIAS_DUR end) as DIAS_DUR1,
    (case when rn = 1 then INI end) as INI1,
    (case when rn = 1 then FIN end) as FIN1,

    (case when rn = 2 then DIAS_DUR end) as DIAS_DUR2,
    (case when rn = 2 then INI end) as INI2,
    (case when rn = 2 then FIN end) as FIN2,

    (case when rn = 3 then DIAS_DUR end) as DIAS_DUR3,
    (case when rn = 3 then INI end) as INI3,
    (case when rn = 3 then FIN end) as FIN3
FROM CTE

Which gets me each column on where it should be but not grouped. Using GROUP BY displays an error on the CTE select.

rn id_hr  dd    beg          end        dd    beg          end
----------------------------------- ------------------------
1  1      10   05/01/2019   15/01/2019 NULL  NULL         NULL
2  1      NULL NULL         NULL       5     03/02/2019   08/02/2019
1  2      8    07/03/2019   15/03/2019 NULL  NULL         NULL

Is there any way to group them on the second select?

Upvotes: 1

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You have additional columns in the result set that are not in the query. However, this should work:

SELECT ID_HR,
       max(case when rn = 1 then DIAS_DUR end) as DIAS_DUR1,
       max(case when rn = 1 then INI end) as INI1,
       max(case when rn = 1 then FIN end) as FIN1,

       max(case when rn = 2 then DIAS_DUR end) as DIAS_DUR2,
       max(case when rn = 2 then INI end) as INI2,
       max(case when rn = 2 then FIN end) as FIN2,

       max(case when rn = 3 then DIAS_DUR end) as DIAS_DUR3,
       max(case when rn = 3 then INI end) as INI3,
       max(case when rn = 3 then FIN end) as FIN3
FROM CTE
GROUP BY ID_HR;

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Yes, you can GROUP BY all the non-CASE columns, and apply MAX to each of the CASE-expression columns.

Upvotes: 1

Related Questions