locked
Query RRS feed

  • 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

    Monday, October 22, 2018 11:13 AM

Answers

  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 22, 2018 12:40 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 22, 2018 6:35 PM

All replies

  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 22, 2018 12:40 PM
  • User-1499457942 posted

    Hi

      Is it not possible without cte

    Thanks

    Monday, October 22, 2018 6:16 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 22, 2018 6:35 PM