CODEWITHSUNDEEP
sqlsql-server-2008
Nick Heidke
Nick Heidke

Reputation: 2847

How to pivot rows to columns with known max number of columns

I have a table structured as such:

Pricing_Group

GroupID     |   QTY
TestGroup1  |   1
TestGroup1  |   2
TestGroup1  |   4
TestGroup1  |   8
TestGroup1  |   22
TestGroup2  |   2
TestGroup3  |   2
TestGroup3  |   5

What I'm looking for is a result like this:

Pricing_Group

GroupID     |   QTY1    |   QTY2    |   QTY3    |   QTY4    |   QTY5
TestGroup1  |   1       |   2       |   4       |   8       |   22
TestGroup2  |   2       |   NULL    |   NULL    |   NULL    |   NULL
TestGroup3  |   2       |   5       |   NULL    |   NULL    |   NULL

Note that there can only ever be a maximum of 5 different quantities for a given GroupID, there's just no knowing what those 5 quantities will be.

This seems like an application of PIVOT, but I can't quite wrap my head around the syntax that would be required for an application like this.

Thanks for taking the time to look into this!

Upvotes: 3

Views: 175

Answers (3)

Robert L Davis
Robert L Davis

Reputation: 476

Perfect case for pivot and you don't need a CTE:

Declare @T Table (GroupID varchar(10) not null,
    QTY int)

Insert Into @T
Values ('TestGroup1', 1),
    ('TestGroup1', 2),
    ('TestGroup1', 4),
    ('TestGroup1', 8),
    ('TestGroup1', 22),
    ('TestGroup2', 2),
    ('TestGroup3', 2),
    ('TestGroup3', 5)

Select GroupID, [QTY1], [QTY2], [QTY3], [QTY4], [QTY5]
From (Select GroupID, QTY, 
    RowID = 'QTY' + Cast(ROW_NUMBER() Over (Partition By GroupID Order By QTY) as varchar)
    from @T) As Pvt
Pivot (Min(QTY)
    For RowID In ([QTY1], [QTY2], [QTY3], [QTY4], [QTY5])
    ) As Pvt2

Upvotes: 4

nathan_jr
nathan_jr

Reputation: 9292

You can also use case statement to perform the pivot:

declare @t table ( GroupID varchar(25), QTY int)
insert into @t
    values  ('TestGroup1', 1),
            ('TestGroup1', 2),
            ('TestGroup1', 4),
            ('TestGroup1', 8),
            ('TestGroup1', 22),
            ('TestGroup2', 2),
            ('TestGroup3', 2),
            ('TestGroup3', 5)

;with cte_Stage (r, GroupId, QTY)
as  (   select  row_number() over(partition by GroupId order by QTY ),
                GroupId,
                QTY
        from    @t
    )
select  GroupId,
        [QTY1] = sum(case when r = 1 then QTY else null end),
        [QTY2] = sum(case when r = 2 then QTY else null end),
        [QTY3] = sum(case when r = 3 then QTY else null end),
        [QTY4] = sum(case when r = 4 then QTY else null end),
        [QTY5] = sum(case when r = 5 then QTY else null end),
        [QTYX] = sum(case when r > 5 then QTY else null end)
from    cte_Stage
group
by      GroupId;

Upvotes: 1

Alex K.
Alex K.

Reputation: 175826

You can pivot on a generated rank;

;with T as (
    select
        rank() over (partition by GroupID order by GroupID, QTY) as rank,
        GroupID, 
        QTY
    from
        THE_TABLE
)
select 
    * 
from 
    T
pivot (
    max(QTY) 
    for rank IN ([1],[2],[3],[4],[5])
) pvt

>> 
GroupID     1     2     3     4     5
----------------------------------------
TestGroup1  1     2     4     8     22
TestGroup2  2     NULL  NULL  NULL  NULL
TestGroup3  2     5     NULL  NULL  NULL

Upvotes: 3

Related Questions