CODEWITHSUNDEEP
sqlsql-serverpivot
Joey
Joey

Reputation: 2792

sqlserver - combine multiple rows into one rows by using pivot

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:

  1. 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.

  2. 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

Upvotes: 0

Views: 100

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 1

Related Questions