CODEWITHSUNDEEP
sqlsql-server
user6413469
user6413469

Reputation: 29

SQL Query-Transform the below table to required format

Need help on below questions.Please provide some pointers/suggestions.

Transform the below table to required format shown below:

Transform table I created the table at below link: https://rextester.com/EEFTL51608

Upvotes: 1

Views: 124

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need unpivot & pivot so i would do :

select year, max(case when t.product = 'Cars' then val end) as Cars,
             max(case when t.product = 'shirts' then val end) as shirts,
             max(case when t.product = 'Pens' then val end) as Pens,
             max(case when t.product = 'Flowers' then val end) as Flowers
from table t cross apply
     ( values ('2012', [2012]), ('2013', [2013]), ('2014', [2014]), ('2015', [2015])
     ) tt(year, val)
group by year;

Upvotes: 0

Squirrel
Squirrel

Reputation: 24793

Note that i have changed the value to integer. If the data type in your original table is not numeric, you will required another step to convert to numeric so that it is able to sum up in the final result

Create table ProdTable
(    
     Product nvarchar(50),
     [2012] int,
     [2013] int ,
     [2014] int ,
     [2015] int ,
)
GO
Insert into ProdTable values ('Cars','100','125','200','175');
Insert into ProdTable values ('shirts','125','75','100','155');
Insert into ProdTable values ('Cars','75','115','100','255');
Insert into ProdTable values ('Pens','140','100','105','185');
Insert into ProdTable values ('Flowers','155','120','145','165');
select * from ProdTable

select *    
from   ProdTable    
       unpivot
       (
           value
           for year in ([2012], [2013], [2014], [2015])
       ) u
       pivot
       (
           sum(value)
           for Product in ([Cars], [shirts], [Pens], [Flowers])
       ) p

Upvotes: 2

Related Questions