pivot - to reduce rows

arkiboys 9,686 Reputation points
2022-05-05T14:43:05.437+00:00

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
...
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
    2022-05-06T20:11:31.697+00:00

    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 .

    199842-image.png

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


    • 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