pivot - to reduce rows

Question

Hello,
I require help in pivot transformation settings to produce the tblResult from tblMain as you see below with sample data
This will produce more columns but fewer rows.

Thank you

tblMain

note: column Name has values without underscore. i.e. it has spaces as you see below

v_Num   m_name      Name        ml_Name     Doc_Name    mo_estimated    mo_actual   fieldWeight fieldHeight
-----------------------------------------------------------------------------------------------------------------------------------
xxx 4 M     t Date      source      uk      2201-04-07  2021-04-04  123.1       77.8
xxx 4 M     v Date      source      uk      2021-01-30  NULL        123.1       77.8
xxx 4 M     t Date      destination france      2021-04-07  2021-04-04  123.1       77.8
xxx 4 M     v Date      destination france      2021-01-30  NULL        123.1       77.8
xxx 4 M     l Date      source      uk      2021-01-30  NULL        123.1       77.8
xxx 4 M     l Date      destination france      2021-01-30  NULL        123.1       77.8
...
xyz
...


tblResult:
v_Num,,m_name, mo_actual_t Date,mo_actual_l Date,mo_actual_v Date,mo_estimated_t Date,mo_estimated_l Date,mo_estimated_v Date,Doc_Name_source,Doc_Name_destination,fieldWeight,fieldHeight
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxx,    4 M 2021-04-04, NULL,   NULL, 2201-04-07, NULL, NULL, uk, france, 123.1, 77.8
...

Answer 1

Hello @arkiboys ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is how to use a pivot transformation , please do let us know if its not accurate.
You will have to play around with the data points as the one whcih you have suggested is very small dataset . Basically when you pivot data you will add some group by clause and also transform some row level to column data . Please do see the screenshot below for a different implementation and it should help .

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators