CODEWITHSUNDEEP
sqlsql-servert-sql
user4365176
user4365176

Reputation: 31

Insert records from multiple rows of table to multiple columns of other table

I have an existing table structure with sample data like

Id BookingId Value TypeId AddedTime
1 100 10 T1 2021-03-22 08:51:52.6333333
2 100 20 T2 2021-03-22 08:50:55.8133333
3 100 30 T3 2021-03-22 08:50:22.1033333
4 200 50 T1 2021-03-22 08:50:22.1033333
5 200 60 T2 2021-03-22 08:50:22.1000000
6 200 70 T3 2021-03-22 08:50:22.0800000

and now data model is changed and it becomes like

Id BookingId Type1Value Type2Value Type3Value AddedTime

Please help me what would be query to copy data from previous table to new table.

Output should be something like

Id BookingId Type1Value Type2Value Type3Value AddedTime
1 100 10 20 30
2 200 50 60 70

I tried:

select BookingId
    , Type1Value = max(case when RN=1 then Value else null end)
    , Type2Value = max(case when RN=2 then Value else null end)
    , Type3Value = max(case when RN=3 then Value else null end)
from (
    select *
        , rn = Row_Number() over (Partition By TypeId Order by AddedTime) 
    from Values_M
) a
where rn <= 3
group by BookingId

Upvotes: 0

Views: 148

Answers (3)

NaveenPrasath Ramesh
NaveenPrasath Ramesh

Reputation: 11

select BookingId, min(T1) as Type1Value, min(T2) as Type2Value, min(T3) as Type3Value
from table1
pivot (sum(value) for Typeid in (T1,T2,T3)) as PivotTable
group by BookingId

Upvotes: 1

Dale K
Dale K

Reputation: 27224

You can use row_number() and self join.

with cte as (
    select Id, BookingId, [Value], TypeId, AddedTime
        , row_number() over (partition by BookingId order by id asc) rn
    from Values_M
)
select C1.rn, C1.BookingId, C1.[Value] Type1Value, C2.[Value] Type2Value, C3.[Value] Type3Value, C1.AddedTime
from cte C1
inner join cte C2 on C2.BookingId = C1.BookingId and C2.rn = 2
inner join cte C3 on C3.BookingId = C1.BookingId and C3.rn = 3
where C1.rn = 1
order by BookingId asc;

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

This will gives you the required result using conditional case expression.

Using row_number() to generate new running number Id

select Id         = row_number() over (order by BookingId),
       BookingId  = BookingId,
       Type1Value = max(case when TypeId = 'T1' then Value end),
       Type2Value = max(case when TypeId = 'T2' then Value end),
       Type3Value = max(case when TypeId = 'T3' then Value end),   
       AddedTime  = min(AddedTime)
from   Values_M
group by BookingId

dbfiddle

Upvotes: 3

Related Questions