If you only want to show a fixed number of columns, then following appoach can be used instead of using a dynamic SQL Pivot query
;with cte as (
select *, rn = ROW_NUMBER() over (partition by DocumentNo order by [LineNo]) from TestData
)
select
Documentno as [Document No],
max(case when rn = 1 then [Location] end) as [Location],
max(case when rn = 2 then [Location] end) as [Location2]
from cte
group by Documentno
If you want to write above SQL code dynamically, you can use following script. So you can use it without any limitation in location column count
declare @sql nvarchar(max)
DECLARE @max_rn int
DECLARE @i int = 1
;with cte as (
select *, rn = ROW_NUMBER() over (partition by DocumentNo order by [LineNo]) from TestData
)
select @max_rn = max(rn) from cte
select @max_rn
set @sql = N'
;with cte as (
select *, rn = ROW_NUMBER() over (partition by DocumentNo order by [LineNo]) from TestData
)
select
Documentno as [Document No],
'
while @i <= @max_rn
begin
set @sql = @sql + N'
max(case when rn = ' + CONVERT(varchar(2),@i) + ' then [Location] end) as [Location' + CONVERT(varchar(2),@i) + ']
' + case when @i = @max_rn then '' else ',' end
set @i = @i + 1
end
set @sql = @sql + N'
from cte
group by Documentno'
exec sp_executesql @SQL
CREATE TABLE mytable(
[LineNo] INT NOT NULL
,Documentno INT NOT NULL
,Location VARCHAR(9) NOT NULL
,
);
INSERT INTO mytable([LineNo],Documentno,Location) VALUES
(1,10,'Delhi'),
(2,'10','Mumbai'),
(3,11,'Rajasthan'),
(4,11,'Chennai');
select
Documentno ,
max(case when rn = 1 then [Location] end) as [Location],
max(case when rn = 2 then [Location] end) as [Location2]
from
(
select *, ROW_NUMBER() over (partition by DocumentNo order by [LineNo]) rn
from mytable
) t
group by Documentno
drop table mytable
Member
504 Points
1776 Posts
Query
Oct 22, 2018 11:13 AM|JagjitSingh|LINK
Hi
I have data like below
LineNo Documentno Location
1 10 Delhi
2 10 Mumbai
3 11 Rajasthan
4 11 Chennai
I want to show like below
Document No Location Location2
10 Delhi Mumbai
11 Rajasthan Chennai
Thanks
Contributor
6101 Points
1449 Posts
Re: Query
Oct 22, 2018 12:40 PM|eralper|LINK
Is the number of Location columns dynamic?
If you only want to show a fixed number of columns, then following appoach can be used instead of using a dynamic SQL Pivot query
If you want to write above SQL code dynamically, you can use following script. So you can use it without any limitation in location column count
SQL Server 2017
Member
504 Points
1776 Posts
Re: Query
Oct 22, 2018 06:16 PM|JagjitSingh|LINK
Hi
Is it not possible without cte
Thanks
All-Star
123252 Points
10024 Posts
Moderator
Re: Query
Oct 22, 2018 06:35 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm