CODEWITHSUNDEEP
sqlsql-server
DRastislav
DRastislav

Reputation: 1882

getting values from rows to columns

I am struggling with 'pivoting on multiple columns' in ms sql (version 2012).

I would like to get values from structure 

Supplier Nr   SupplierName  Manager             Service           Type_of_Service     Planned_Turnover       Turnover          TransactionID            Month                 
 30              Honda       Name               CityService            varchar          100 000                95 000         009000011440114           201701
 50              Honda22     Name               CityService           varchar           200 000                195 000        0090001101144             201701
 30              Honda       Name               CityService            varchar          130 000                115 000        00900111114               201702
 50              Honda22     Name               CityService           varchar           230 000                205 000        001144                    201702

into desired form: 

Supplier Nr   SupplierName  Manager             Service           Type_of_Service     Planned_Turnover 201701       Turnover 201701         TransactionID 201701  Planned_Turnover 201702       Turnover 201702         TransactionID 201702  
  30              Honda      Name               CityService            varchar              100 000                     95000               009000011440114         130 000                     115 000                     00900111114         
  50              Honda22    Name               CityService           varchar               200 000                     195000              0090001101144           230 000                     205 000                     001144

i understand, how to pivot table based on one column - e.g. get turnover to columns month by month ... but how to get more values (Planned_Turnover, Turnover, TransactionID next to each other?)

Thanks

Upvotes: 2

Views: 91

Answers (1)

LukStorms
LukStorms

Reputation: 29647

Here is an example that uses a temporary table and a dynamic SQL with a pivot for 2 dates. 

create table #tempTable ([Supplier Nr] int, SupplierName varchar(30), Manager varchar(30), [Service] varchar(30), Type_of_Service varchar(30), Planned_Turnover varchar(30), Turnover varchar(30), TransactionID varchar(30), [Month] int);

insert into #tempTable ([Supplier Nr],SupplierName,Manager,[Service],Type_of_Service,Planned_Turnover,Turnover,TransactionID,[Month]) values
(30,'Honda','Name','CityService','varchar','100 000','95 000','009000011440114',201701),
(50,'Honda22','Name','CityService','varchar','200 000','195 000','0090001101144',201701),
(30,'Honda','Name','CityService','varchar','130 000','115 000','00900111114',201702),
(50,'Honda22','Name','CityService','varchar','230 000','205 000','001144',201702);

DECLARE @SQLString varchar(max);  

SET @SQLString = 'select * 
from (
  select [Supplier Nr], SupplierName, Manager, [Service], Type_of_Service, concat(''Planned_Turnover '',[Month]) as Title, Planned_Turnover as Value 
  from #tempTable where [Month] in (@Date1, @Date2)
  union all
  select [Supplier Nr], SupplierName, Manager, [Service], Type_of_Service, concat(''Turnover '',[Month]) as Title, Turnover as Value 
  from #tempTable where [Month] in (@Date1, @Date2)
  union all
  select [Supplier Nr], SupplierName, Manager, [Service], Type_of_Service, concat(''TransactionID '',[Month]) as Title, TransactionID as Value 
  from #tempTable where [Month] in (@Date1, @Date2)
) q
PIVOT (MAX(Value) FOR Title IN 
([Planned_Turnover @Date1],[Turnover @Date1],[TransactionID @Date1],[Planned_Turnover @Date2],[Turnover @Date2],[TransactionID @Date2])
) pvt';

declare @Date1 varchar(6) = '201701';
declare @Date2 varchar(6) = '201702';

SET @SQLString = replace(replace(@SQLString,'@Date1',@Date1),'@Date2',@Date2);

exec (@SQLString);

This is limited to 2 months. If it were for a range of months then it's better to just calculate a varchar for the pivot fields, and use it in the @SQLString.
Example code:

declare @Date1 varchar(6) = '201712';
declare @Date2 varchar(6) = '201802';

declare @PivotFields varchar(max); 
with cte as (
   select convert(date,concat(@Date1,'01')) as dt
   union all
   select dateadd(month,1,dt) from cte where dt < convert(date,concat(@Date2,'01'))
)
select @PivotFields = concat(@PivotFields+', ',quotename(word+' '+FORMAT(dt,'yyyyMM')))
from cte cross join (values (1,'Planned_Turnover'),(2,'Turnover'),(3,'TransactionID')) words(wordid, word)
order by dt, wordid;

-- result: @PivotFields="[Planned_Turnover 201712], [Turnover 201712], [TransactionID 201712], [Planned_Turnover 201801], [Turnover 201801], [TransactionID 201801], [Planned_Turnover 201802], [Turnover 201802], [TransactionID 201802]"

Upvotes: 2

Related Questions