sqlserver - combine multiple rows into one rows by using pivot

Question

Assume we have the following table:

ID      Type    Amount
0001     A       10
0001     B       20
0001     C       30
0002     A       15
0002     C       20
0003     B       40
...

The desired result will be:

ID      Type A   Type B    Type C 
0001     10       20        30

I can do the following in order to combine multiple rows into one row.

SELECT ID, A, B, C
FROM  MyTable 
PIVOT
(
      SUM(amount)
       FOR Type IN (A, B, C)
) AS P

However, my table is more complicated that the previous one. Assume my table is the following:

ID      Type    Total_Amount   Average_Amount
0001     A       10                0.5
0001     B       20                0.7
0001     C       30                0.9

So my question is how to produce the following results?

ID      TypeA_total  TypeB_total   TypeC_total TypeA_avg  TypeB_avg   TypeC_avg 
0001     10           20              30        0.5         0.7          0.9

Update:

The number of types is fixed. It means there are only Type A, B and C. Some ids may only have only 1 type. Some may have all 3 types. Some others may have 2 types.
Total_amount and Average_amount are given in the original table. I don't need to do calculation.

Here is the link that you can play with the table: http://sqlfiddle.com/#!3/4e6835

Do you need to find the average of averages or are you summing those as well? The first table shows Total_Amount, while the first just show Amount and you are summing that up. For your second query are you working with already aggregated results or no? — Tom H
– Tom H, Commented Jan 5, 2016 at 17:55

Pரதீப் · Accepted Answer · 2016-01-05 18:07:38Z

1

You need to unpivot the data before pivoting

WITH cte 
     AS (SELECT id, 
                value, 
                col_name 
         FROM   yourtable 
                CROSS apply( VALUES (total_amount,'Type' + type + '_Total'), 
                                    (average_amount,'Type' + type + '_Avg') ) 
             CS (value, col_name)) 
SELECT * 
FROM   cte 
       PIVOT (Max(value) 
             FOR col_name IN([TypeA_total],[TypeB_total],[TypeC_total], 
                             [TypeA_avg],[TypeB_avg],[TypeC_avg]))pv

Note: If no. of types are unknown then you need use dynamic sql

edited Jan 5, 2016 at 18:07

answered Jan 5, 2016 at 17:49

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

1 Comment

Joey Over a year ago

I run your first select statement separately and see the results first. Now, I think I understand what you mean here by "unpivot" the data first, and then pivot them.

Sean Lange · Accepted Answer · 2016-01-05 17:55:03Z

Here is another way to do it. This is using a crosstab query instead of a PIVOT. I find the syntax less obtuse and it even has a slight performance gain over using PIVOT.

if OBJECT_ID('tempdb..#something') is not null
    drop table #something

create table #something
(
    ID char(4)
    , SomeType char(1)
    , Amount int
    , AverageAmount decimal(9, 2)
)

insert #something
select '0001', 'A', 10, .5 union all
select '0001', 'B', 20, .7 union all
select '0001', 'C', 30, .9;

with OrderedResults as
(
    select *
        , ROW_NUMBER() over(partition by ID order by SomeType) as RowNum
    from #something
)

select ID
    , MAX(Case when RowNum = 1 then SomeType end) as TypeA
    , MAX(Case when RowNum = 1 then AverageAmount end) as AverageA
    , MAX(Case when RowNum = 2 then SomeType end) as TypeB
    , MAX(Case when RowNum = 2 then AverageAmount end) as AverageB
    , MAX(Case when RowNum = 3 then SomeType end) as TypeC
    , MAX(Case when RowNum = 3 then AverageAmount end) as AverageC
from OrderedResults
group by ID

Collectives™ on Stack Overflow

sqlserver - combine multiple rows into one rows by using pivot

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related