Pivot/transpose rows into columns efficiently with multiple columns

Question

Let's say I have data that looks liek this:

  Table
  Num1       Type1       Code       Group      DA        Account      Value
  1X2        GG          XX1        INTS       1         123          75.00
  1X2        GG          XX1        INTS       1         234          100.00

What's I'm trying to do is Pivot the data so it looks like this:

  Num1       Type1       Code       Group      DA      123        234
  1X2        GG          XX1        INTS       1       75.00      100.00

I'm not really sure how something like this can be accomplished with PIVOT, but I did try the below approach:

 Select Num1, 
        Type1, 
        Code, 
        Group, 
        DA, 
        '123' = (Select Value from Table t2 where t1.num1 = t2.num1 and Account = 123 ),
        '234' = (Select Value from Table t2 where t1.num1 = t2.num1 and Account = 234 )
 From Table t1
  

However the error I'm getting is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

In that case I added "Top 1" to each subquery:

Select Num1, 
        Type1, 
        Code, 
        Group, 
        DA, 
        '123' = (Select TOP 1 Value from Table t2 where t1.num1 = t2.num1 and Account = 123 ),
        '234' = (Select TOP 1 Value from Table t2 where t1.num1 = t2.num1 and Account = 234 )
 From Table t1

However, even though the query now returns two rows with AMOUNTS for each Account, I'm not fully understanding the purpose of TOP 1, but basically now the data looks like this:

 Num1       Type1       Code       Group      DA        123        234
  1X2        GG          XX1        INTS       1        NULL       100.00 
  1X2        GG          XX1        INTS       1        75.00      NULL

And I guess that's not bad, because I can do a MAX(123) and MAX(234) with GROUP BY on all the other columns, and end up with 1 row.

Is there a better approach to this? Can this be achieved with PIVOT?

Answer 1

SELECT
  [Num1], 
  [Type1], 
  [Code], 
  [Group], 
  [DA], 
  [123],
  [234]
FROM
  yourTable
PIVOT
(
  MAX([value])  
  FOR [account] IN ([123], [234])
)
  AS PivotTable

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7fbe16b9254aa5ee60a23e43eec9597f