CODEWITHSUNDEEP
sqlpivot
spiderman
spiderman

Reputation: 1585

Simple SQL Pivot query with unknown number of columns

I have this sample data:

Item  Location
A   K
A   X
B   W
B   Z
B   Y
C   L

I'd like to have a result like this:

Item    Loc1    Loc2    Loc3
A       K       X
B       W       Z       Y
C       L

Is it possibile using SQL Pivot operator?

Upvotes: 0

Views: 247

Answers (2)

Dwight Reynoldson
Dwight Reynoldson

Reputation: 960

Here is one method of using pivot query syntax with dynamic sql to generate the columns in the format you require.

Firstly I use a table variable to store the column names, this is generated using a window function that partitions on the item and then orders on the location and combining that with 'Loc' to generate Loc1, Loc2 etc.

Then I pipe these into a variable which will form the column list. This is done with a while loop to add each new column sequentially.

From there I tidy up the column list to remove trailing commas.

After that I generate the sql statement parsing the column variable in at the relevant points. I then execute this which generates the required result set.

create table #table (
    item char(1),
    location char(1)
);

insert #table (item, location)
values
('A',   'K'),
('A',   'X'),
('B',   'W'),
('B',   'Z'),
('B',   'Y'),
('C',   'L');

declare @pivotcols nvarchar(max)='';
declare @sql nvarchar(max)='';

declare @cols table (
colname nvarchar(150),
rowno int
);


insert @cols
select distinct '[Loc'+cast(rowno as nvarchar(10))+'],', rowno from
(
select row_number()over(partition by item order by location) as rowno,
       item, location 
from #table t)colgenerator;

while exists (select 1 from @cols)
    begin
    select top 1 @pivotcols = @pivotcols+colname from @cols order by rowno;
    delete from @cols where rowno in (select top 1 rowno from @cols order by rowno);
    end


select @pivotcols=substring(@pivotcols, 1, len(@pivotcols)-1);

select @sql=N'select item, '+@pivotcols+'
from (
select rowno, item, location from
(
select ''Loc''+cast(row_number()over(partition by item order by location) as nvarchar(1000)) as rowno,
       item, location 
from #table t)x)src
pivot (max(location) for rowno in ('+@pivotcols+')) piv;';


exec(@sql);

drop table #table;

enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I prefer conditional aggregation:

select item,
       max(case when seqnum = 1 then location end) as location_1,
       max(case when seqnum = 2 then location end) as location_2,
       max(case when seqnum = 3 then location end) as location_3
from (select t.*, 
             row_number() over (partition by loc order by loc) as seqnum
      from t
     ) t
group by item;

Upvotes: 1

Related Questions