0

I have the table called as sales with three columns SalesCountry , SalesState and SalesMan as shown below:

Table:

create table sales
(
     SalesCountry varchar(20),
     SalesState varchar(20),
     SalesMan varchar(20)
);

Insertion:

insert into sales values('USA','TEXAS','Mak');
insert into sales values('USA','California','Sam');
insert into sales values('Cannada','Alberta','John');
insert into sales values('Cannada','Manitoba','John');

Now I want to show the result as follows:

County      State1      State2      SalesMan1   SalesMan2
---------------------------------------------------------
USA         TEXAS       California  Mak         Sam
Cannada     Alberta     Manitoba    John        NULL

Note: The pivot query should be dynamic, which should show the columns as per the rows in the table with increment of value 1 with column name like i have shown State1,State2 there may be State3,State4,,,,,,n. In my example there are only four rows, there may be more than that also.

MAK
  • 6,824
  • 25
  • 74
  • 131
  • Possible duplicate of [SQL Server : dynamic pivot over 5 columns](http://stackoverflow.com/questions/18657214/sql-server-dynamic-pivot-over-5-columns) – Tab Alleman Feb 11 '16 at 14:04
  • how would the final result be if we : `insert into sales values('USA','California','John');` ? would it produce SalesMan3 column ? and also, `insert into sales values('USA','Kansas','Mak');` ? – armen Feb 11 '16 at 14:07

1 Answers1

0

using PIVOT we can achieve this 

    DECLARE @sales table 
    (
         SalesCountry varchar(20),
         SalesState varchar(20),
         SalesMan varchar(20)
    );

    insert into @sales values('USA','TEXAS','Mak');
    insert into @sales values('USA','California','Sam');
    insert into @sales values('Cannada','Alberta','John');
    insert into @sales values('Cannada','Manitoba','John');  

Select SalesCountry,MAX([1]) State1,MAX([2]) State2,MAX([Sales_1])[Sales_1],MAX([Sales_2])[Sales_2]  FROM (
select 
SalesCountry,
SalesState,
SalesMan,
ROW_NUMBER()OVER(PARTITION BY SalesCountry ORDER BY SalesCountry)RN ,
'Sales'+'_'+CAST(ROW_NUMBER()OVER(PARTITION BY SalesCountry ORDER BY SalesCountry) AS VARCHAR)RNN
    FROM @sales
    )T
PIVOT (MAX(SalesState) for RN IN ([1],[2]))P
PIVOT (MAX(SalesMan) for RNN IN ([Sales_1],[Sales_2]))PP
GROUP BY PP.SalesCountry
mohan111
  • 8,633
  • 4
  • 28
  • 55