pivot - to reduce rows

arkiboys 9,686 Reputation points

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


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

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
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,067 questions
0 comments No comments
{count} votes

Accepted answer
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

    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.

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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

0 additional answers

Sort by: Most helpful