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

Question

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

Please show us the query you wrote using pivot and stuff. And clarify, are there always 3 rows to combine or might there sometimes be only 1 or 2? And what should be displayed for AddedTime? — Dale K
– Dale K, Commented Mar 25, 2021 at 3:52
Actually from UI all 3 records will post in one go, so AddedTime for migrated records may be the first AddedTime of each group. Secondly always there will be 3 rows to be combined — user4365176
– user4365176, Commented Mar 25, 2021 at 4:04
@Squirrel's suggestion is valid. What is driving the structural change to this table? It would generally be advisable to keep the structure that you have, rather than flattening based on "today's" structure ...... you can almost guarantee that Type4, Type5, Type6 values will come along at some point in the future - then the table structure will need to keep changing each time a functional change occurs. Not ideal — Craig
– Craig, Commented Mar 25, 2021 at 4:11
What if you keep the current table design and create a view to return the data in the required format ? — Squirrel
– Squirrel, Commented Mar 25, 2021 at 5:56

Squirrel · Accepted Answer · 2021-03-25 06:03:49Z

3

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

answered Mar 25, 2021 at 6:03

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4365176 Over a year ago

thanks @Squirrel, this gives expected result.

Dale K · Accepted Answer · 2021-03-25 07:10:16Z

1

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

edited Mar 25, 2021 at 7:10

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Mar 25, 2021 at 6:40

NaveenPrasath Ramesh

111 bronze badge

1 Comment

endo64 Over a year ago

A little explanation is always nice, instead of code only answers.

Dale K · Accepted Answer · 2021-03-25 06:09:09Z

0

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;

answered Mar 25, 2021 at 6:09

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

1 Comment

user4365176 Over a year ago

Thanks @DaleK this gives too expected result

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related