How to split values for the same id into columns

Goffer 0
Viewed 44

How to split values for the same id into columns?

Example Table

enter image description here

I want to achieve this:

enter image description here

I try:

 SUM (CASE WHEN Type = 1 THEN Price END) AS Type_1
 SUM (CASE WHEN Type = 2 THEN Price END) AS Type_2
 SUM (CASE WHEN Type = 3 THEN Price END) AS Type_3
 --SUM (CASE WHEN Type = 1 THEN CarsID END) AS CarsID_Type1
 --SUM (CASE WHEN Type = 2 THEN CarsID END) AS CarsID_Type2
 --SUM (CASE WHEN Type = 3 THEN CarsID END) AS CarsID_Type3
GROUP BY ID

3 additional columns (Type_1, Type_2, Type_3) are correctly created and everything is in one line. Unfortunately, the last commented out part causes an error:

Operand data type uniqueidentifier is invalid for sum operator.

What to replace with SUM to make the query run correctly.

I will be grateful for your help.

sql
1 Answers

This query is more complex then I thought, but it will do what you want for any kind of id. But there is a problem, it was construct to work with the max of 3 different "types". If your column "Type" have more then 3 different values for the same "id" you will need to adapt the code below.

/*Shifting the value to another column*/
with first_lag as (
select 
id 
, type_
, case when count(type_)over(partition by id) > 1 then lag(type_)over(order by type_ desc) 
end as lag_type_1
, CarsID
, case when count(CarsID)over(partition by id) > 1 then lag(CarsID)over(order by CarsID desc ) 
end as lag_cars_1
, price::int
from stack_overflow so 
)
/*Shifting again the value to another column*/
, second_lag as(
select 
    id
    , type_
    , lag_type_1
    , lag(lag_type_1)over(order by lag_type_1 desc) lag_type_2
    , CarsID
    , lag_cars_1
    , lag(lag_cars_1)over(order by lag_cars_1 desc) lag_cars_2
    , sum(price) over(partition by id) as price_by_id
from first_lag
)
/*Counting how many "types" the same id have (preparing to filter)*/
, counting_rows as (
select 
    *
    , count(type_)over(partition by type_) +count(lag_type_1) over(partition by type_) +count(lag_type_2) over(partition by type_) as counting
from second_lag
)
/*Knowing which row have the max number of "types" (preparing to filter)*/
, selecting_max as (
select 
    *
    ,max(counting)over(partition by id) as max_flag
from counting_rows
)
/*Selecting the columns and filtering just the row with the max number of "types"*/
select id,type_,lag_type_1,lag_type_2,carsid,lag_cars_1,lag_cars_2,price_by_id
from selecting_max
where counting = max_flag
--group by id

Note it will work for different ids. (But only if it has 3 or less different"types") Image

userpdMe10
Related