How to generate column based on order id in sql

Question

I have two tables: customerTable and orderTable that are shown below. I need to generate order column dynamically.

CustomerTable

custId CustName
01     Suresh
02     Ramesh

OrderTable

custId  OrderId 
01      011
01      012

need output as like :

custId Order1 Order2
01     011    012

Here if order is multiple times for each customer then columns will be added dynamically.

If custId = 01 has 011,012,013,014...... has many order then table will be like

custId order1  order2 order3 order4 order5 order6 ...... many oder N columns 
01     011       012   013   014    015    06     ....... 0N..

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Gurwinder Singh, Jan 21 '17 at 10:04
Do you know the total or maximum number of orders a customer can have? If not, this isn't a good approach; generally SQL's designed around the concept of query results having a fixed number of columns with a variable number of rows. — JohnLBevan, Jan 21 '17 at 10:07
@GurV user mentioned he needs order id dynamicaly and then after that pivot will come into picture — Yashveer Singh, Jan 21 '17 at 10:07
there are no fixed columns for order , it will be multiples , i am working with complex queries , need better solution so i can work with that , is PIVOT better solution, or is there any solution except this pivot. — yash fale, Jan 21 '17 at 10:17

score 1 · Answer 1 · answered Jan 21 '17 at 10:15

What you are looking for is called pivoting. See the documentation.

A simple example taken from the link above:

USE AdventureWorks2008R2;
GO
SELECT
    DaysToManufacture,
    AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

Results in:

DaysToManufacture | AverageCost
----------------------------------------
0                 | 5.0885
1                 | 223.88
2                 | 359.1082
4                 | 949.4105

These are rows in the source table grouped, no pivoting done yet. By pivoting, you turn the values of the rows in column #1 into columns, and the values of the rows in column #2 get 'rotated' and appear as one row. The SQL to do that is the following:

-- Pivot table with one row and five columns
SELECT
    'AverageCost' AS Cost_Sorted_By_Production_Days, 
    [0],
    [1],
    [2],
    [3],
    [4]
FROM
(
    SELECT
        DaysToManufacture,
        StandardCost 
    FROM
        Production.Product
) AS SourceTable
PIVOT
(
    AVG(StandardCost)
    FOR DaysToManufacture IN
    (
        [0],
        [1],
        [2],
        [3],
        [4]
    )
) AS PivotTable;

This results in output such as:

Cost_Sorted_By_Production_Days | 0      | 1      | 2        | 3    | 4
-----------------------------------------------------------------------------
AverageCost                    | 5.0885 | 223.88 | 359.1082 | NULL | 949.4105

Gurwinder Singh · Accepted Answer · 2017-01-21T10:38:46.447

Do this dynamically like this:

declare @sql as nvarchar(max)
declare @cols as nvarchar(max)

set @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.OrderId)
            FROM OrderTable c
            order by 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @sql = STUFF((SELECT ',' + QUOTENAME(OrderId) + ' as Order' + convert(varchar,row_number() over (order by OrderId))
            from (
                select distinct OrderId 
                FROM OrderTable
            ) t
            order by 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @sql = 'SELECT custId, ' + @sql + ' from 
            (
                select *
                from OrderTable
           ) x
            pivot 
            (
                 max(OrderId)
                for OrderId in (' + @cols + ')
            ) p '
exec(@sql)

Produces:

To be noted here is that, it works correctly for one custId as the columns are aligned based on the the OrderId in the pivoted result.

How to generate column based on order id in sql

2 Answers2