CODEWITHSUNDEEP
sql-serverssmspivot
Koosh
Koosh

Reputation: 896

Pivot/transpose rows into columns efficiently with multiple columns

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?

Upvotes: 3

Views: 1211

Answers (4)

heather
heather

Reputation: 21

MatBailie and Larnu are 100% correct! However, if you ever need to do this dynamically (e.g. you don't know the number of accounts to be summarized), dynamic SQL can be used. The syntax for your example is provided below:

    IF OBJECT_ID('tempdb..#acct_list') IS NOT NULL DROP TABLE #acct_list; -- Deletes #acct_list temp table if it already exists
    SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY Account) rownum, Account -- Lists each account with a row number for looping
    INTO #acct_list -- Creates and inserts data into a #acct_list temp table
    FROM Table 
    ORDER BY Account;

    DECLARE @col INT = 1; -- Counter value used to keep track of which account is being aggregated
    DECLARE @cnt INT = (SELECT COUNT(DISTINCT Account) FROM Table); -- Counts the number of unique accounts
    DECLARE @sql NVARCHAR(MAX) = N'SELECT Num1, Type1, Code, [Group], DA'; -- Writes the SQL string to be executed
    WHILE @col <= @cnt -- Loops through columns until no more exist
        BEGIN
            -- Identifies the current account
            DECLARE @acct VARCHAR(3) = (SELECT Account FROM #acct_list WHERE rownum = @col);
            -- Writes SQL syntax for the current account column
            SET @sql += ' ,MAX(CASE Account WHEN ''' + @acct + ''' THEN Value END) [' + @acct + ']'
            -- Increment the counter to advance to the next column
            SET @col += 1;
        END;
    SET @sql += ' FROM Table GROUP BY Num1, Type1, Code, [Group], DA'
    --PRINT @sql
    EXEC SYS.SP_EXECUTESQL @sql;

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

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

Upvotes: 1

nbk
nbk

Reputation: 49395

For that you need aggregation Functions Like MAX And a GROUP BY

CREATE TABLE  Table1
    ([Num1] varchar(3), [Type1] varchar(2), [Code] varchar(3), [Group] varchar(4), [DA] int, [Account] int, [Value] DECIMAL(10,2))
;
    
INSERT INTO  Table1
    ([Num1], [Type1], [Code], [Group], [DA], [Account], [Value])
VALUES
    ('1X2', 'GG', 'XX1', 'INTS', 1, 123, 75.00),
    ('1X2', 'GG', 'XX1', 'INTS', 1, 234, 100.00)
;

GO

SELECT
[Num1], [Type1], [Code], [Group],
MAX(CASE WHEN [Account] = 123 THEN [Value] ELSe -999999999999 END) AS [123],
MAX(CASE WHEN [Account] = 234 THEN [Value] ELSe -9999999999 END) AS [234]
FROM Table1
 GROUP BY  [Num1], [Type1], [Code], [Group]
GO

Num1 | Type1 | Code | Group |   123 |    234
:--- | :---- | :--- | :---- | ----: | -----:
1X2  | GG    | XX1  | INTS  | 75.00 | 100.00

db<>fiddle here

Upvotes: 3

Thom A
Thom A

Reputation: 95906

This problem is that to pivot your data you need to aggregate. In this, the value of your column value:

SELECT Num1, 
       Type1, 
       Code, 
       [Group], --GROUP is a reserved keyword and should not be used to object names
       DA, 
       MAX(CASE Account WHEN 123 THEN [Value] END) AS [123],
       MAX(CASE Account WHEN 234 THEN [Value] END) AS [234]
FROM dbo.Table t1
GROUP BY Num1, 
         Type1, 
         Code, 
         [Group], --GROUP is a reserved keyword and should not be used to object names
         DA;

Upvotes: 1

Related Questions