CODEWITHSUNDEEP
sql-servert-sqlpivot-tableself-join
developer
developer

Reputation: 1705

SQL Self Join / Pivot table query

I have the following Table1 in SQL Server 2016: 

SELECT Year, Type, Value From Table1

    Year  Type  Value
    2010  1     10
    2010  2     15
    2010  3     20
    2011  1     100
    2011  2     150
    2011  3     200

I would like to convert it to the following table:

Year  Type1  Type2  Type3
2010  10     15     20
2011  100    150    200

I think we can do either self join or pivot table to achieve this. What is the best way to achieve this?

Upvotes: 1

Views: 808

Answers (4)

Cetin Basoz
Cetin Basoz

Reputation: 23867

CREATE TABLE #myTable (
[Year] int, [Type] int, [Value] int, [ExtraColumn] varchar(10));

INSERT INTO #myTable ([Year], [Type], [Value], [ExtraColumn]) 
VALUES (2010, 1, 10, 'I'), 
       (2010, 2, 15, 'G'), 
       (2010, 3, 20, 'N'), 
       (2011, 1, 100, 'O'), 
       (2011, 2, 150, 'R'), 
       (2011, 3, 200, 'E'); 

select Year, [1] as Type1, [2] as Type2, [3] as Type3 
from ( 
 select [Year], [Type], [Value] 
 from #myTable 
 ) t 
PIVOT ( SUM(Value) FOR [Type] IN ( [1], [2], [3] ) ) pvt;

-- OR
with myData as
( 
 select [Year], [Type], [Value] 
 from #myTable 
)
select Year, [1] as Type1, [2] as Type2, [3] as Type3 
from myData
PIVOT ( SUM(Value) FOR [Type] IN ( [1], [2], [3] ) ) pvt;

drop table #myTable;

Upvotes: 2

Johnny51714
Johnny51714

Reputation: 76

Assuming you always have 3 types, you can use PIVOT in SQL.

Here is an example based on your example:

if object_id('tempdb..#temp1') is not null
    drop table #temp1

create table #temp1 (
     Year  int
     ,Type  int
     ,Value int
)

insert into #temp1 values 
    (2010,1,10),
    (2010,2,15),
    (2010,3,20),
    (2011,1,100),
    (2011,2,150),
    (2011,3,200)

SELECT 
    Year
    , [1] AS Type1
    , [2] AS Type2
    , [3] AS Type3
FROM   
    #temp1 p  
PIVOT  
(  
sum(value)  
FOR type IN  
( [1], [2], [3])  
) AS pvt  
ORDER BY pvt.Year

Here are the results:

Pivot Results

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23867

select * 
from myTable PIVOT ( SUM(Value) FOR [Type] IN ( [1], [2], [3] ) ) pvt;

DbFiddle demo

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Assuming you always have 3 types using conditional aggregation is a simple way to tackle this.

select [Year]
    , Type1 = Max(case when [Type] = 1 then Value end)
    , Type2 = Max(case when [Type] = 2 then Value end)
    , Type3 = Max(case when [Type] = 3 then Value end)
from Table1
group by [Year]
order by [Year]

Upvotes: 1

Related Questions