T-SQL Converting N number of rows to columns

Question

I am look for a good performing T-SQL query to give a large number of Rows N number of columns. But the number of columns will not be the same for each row.

For instance - i have an ordertable like the following with about 1 million rows.

Order
OrderID   Date      
========  ========  
1         20160102  
2         20160402

Each order can have N number of orderlines.

My orderline table has about 1.5 million rows;

OrderLine 
OrderID   OrderLine   ProductID     Amount
========  ========    ==========    ==========
1         1           3245          299.00
2         1           9876          799.00
2         2           5466          899.00
2         3           7752          599.00

I want an endresult like the following:

OrderID   Date     ProductID1  Amount1  ProductID2  Amount2  ProductID3  Amount3       
========  ======== ==========  =======  ==========  =======  ==========  =======
1         20160102  3245        299.00    
2         20160402  9876        799.00   5466        899.00    7752       599.00

The number of orderlines can be large (like 50) for each order.

I guess i could use subselects but that would require N-number of subselects - and that would perform very poorly

SELECT 
 OrderID,
 Date,
 (SELECT ProductID FROM ORDERLINE OI JOIN O ON O.OrderID=OI.OrderID WHERE ORDERLINE=1) AS ProductID1,
 (SELECT Amount    FROM ORDERLINE OI JOIN O ON O.OrderID=OI.OrderID WHERE ORDERLINE=1) AS Amount1,
...
 (SELECT ProductID FROM ORDERLINE OI JOIN O ON O.OrderID=OI.OrderID WHERE ORDERLINE=N) AS ProductIDN,
 (SELECT Amount    FROM ORDERLINE OI JOIN O ON O.OrderID=OI.OrderID WHERE ORDERLINE=N) AS AmountN

FROM 
ORDER O

Another option could perhaps be pivoting. But as I see it - it is not "traditional" pivoting - it would be dynamic pivoting.

But I'm unsure how that would look and perform?

Possible duplicate of SQL Server dynamic PIVOT query?

iamdave
– iamdave

2017-01-05 13:10:10 +00:00
Commented Jan 5, 2017 at 13:10 — iamdave
– iamdave, Commented Jan 5, 2017 at 13:10

Gordon Linoff · Accepted Answer · 2017-01-05 13:10:16Z

1

I would recommend conditional aggregation:

SELECT o.OrderID, o.Date,
       MAX(CASE WHEN ol.orderline = 1 THEN ProductId END) as ProductId1,
       MAX(CASE WHEN ol.orderline = 1 THEN Amount END) as Amount1,
       MAX(CASE WHEN ol.orderline = 2 THEN ProductId END) as ProductId2,
       MAX(CASE WHEN ol.orderline = 2 THEN Amount END) as Amount2,
       . . .
FROM ORDER O JOIN
     ORDERLINE OI
     ON O.OrderID = OI.OrderID
GROUP BY o.OrderID, o.Date;

I wouldn't expect your version to work, unless you have a table called o. The proper syntax would be:

(SELECT ProductID FROM ORDERLINE OI WHERE O.OrderID = OI.OrderID AND ORDERLINE = 1) AS ProductID1,

answered Jan 5, 2017 at 13:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Cappelletti · Accepted Answer · 2017-01-05 13:29:39Z

If you need to go dynamic

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(concat('ProductID',OrderLine))+','+ QuoteName(concat('Amount',OrderLine)) From OrderLine  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [OrderID],[Date],' + @SQL + '
From (
        Select A.OrderID
              ,A.Date
              ,C.*
        From   [Order] A
        Join   OrderLine B on (A.OrderID=B.OrderID)
        Cross  Apply (Values (concat(''ProductID'',B.OrderLine),cast(B.ProductID as varchar(25)))
                            ,(concat(''Amount''   ,B.OrderLine),cast(B.Amount as varchar(25)))
                      ) C (Item,Value)

     ) A
 Pivot (max(Value) For [Item] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

Collectives™ on Stack Overflow

T-SQL Converting N number of rows to columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related