Query [Answered]RSS

• • Reply
  JagjitSingh

  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

• • Reply
  eralper

  Contributor

  6081 Points

  1443 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

  ;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 

  T-SQL programming SQL Server Tutorials
  SQL Server 2017
  
• • Reply
  JagjitSingh

  Member

  504 Points

  1776 Posts

  Re: Query

  Oct 22, 2018 06:16 PM|JagjitSingh|LINK

  Hi

    Is it not possible without cte

  Thanks

• • Reply
  limno

  All-Star

  122824 Points

  9892 Posts

  Moderator

  Re: Query

  Oct 22, 2018 06:35 PM|limno|LINK

  JagjitSingh

  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

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm