Group and Pivot in Synapse Analytics

Question

I have my "Job Name", "Overall Costs", "Cost A" are all under the same column name. Then the column next to them is "Actual Costs", I am wanting to pivot the column with "Job Name", "Overall Costs", "Cost A".

I have attached a a picture with an example of my situation, currently Azure can't differentiate the different costs for each job and when I upload to Power BI it will just group each cost, not link each cost to the job it is under. (I have coloured over the job number and location)

I am wanting to group the data as project title and then pivot the types of costs as a row header so in this example there would be 3 rows with the row headers as 'LAB, Labour' , 'OL, Own Labour' etc...

Answer 1

You will need to group by operation to aggregate your data by job name. After grouping, you will pivot the "Overall Costs" and "Cost A" to become new columns in your dataset using the PIVOT SQL operation.

Once your data is structured correctly in Synapse Analytics, you can import the data into Power BI.

I imagined your query like below :

SELECT [Job Name],
       MAX(CASE WHEN [Cost Type] = 'LAB, Labour' THEN [Actual Costs] END) AS [LAB, Labour],
       MAX(CASE WHEN [Cost Type] = 'OL, Own Labour' THEN [Actual Costs] END) AS [OL, Own Labour],
       MAX(CASE WHEN [Cost Type] = '2H, Internal doors' THEN [Actual Costs] END) AS [2H, Internal doors]
  FROM (SELECT [Job Name], [Cost Type], [Actual Costs] FROM YourTable) AS SourceTable
 GROUP BY [Job Name];