Query

Question

User-1499457942 posted

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

Answer 1

User-595703101 posted

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

;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 

Answer 2

User-1499457942 posted

Hi

  Is it not possible without cte

Thanks

Answer 3

User77042963 posted

Hi

  Is it not possible without cte

Thanks

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