Re: Odd pivot table type query

• Previous message: Shawn: "Odd pivot table type query"
• In reply to: Shawn: "Odd pivot table type query"
• Next in thread: Shawn: "Re: Odd pivot table type query"
• Reply: Shawn: "Re: Odd pivot table type query"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 23 Nov 2004 05:46:46 +0530

For documented method, you will have to make use of procedural code to
achieve this. See following example. OR you will have to use some 3rd party
tool to do it(www.rac4sql.com).

Eg:
-- sample table
-- if object_id('tab') is not null
-- drop table tab

create table tab(ID int,
SEQ_NUM int ,
ROUTE varchar(50))
go
-- sample data
insert into tab values(1 ,1 ,'AA')
insert into tab values(1 ,2 ,'BB')
insert into tab values(1 ,3 ,'CC')
insert into tab values(2 ,1 ,'AA')
insert into tab values(3 ,1 ,'VV')
insert into tab values(3 ,2 ,'XX')
go
-- t-sql code for generating report
if object_id('tempdb..#tmp') is not null
drop table #tmp

create table #tmp(id int, tmpval varchar(50))
go
declare @id int
declare @seq_num int
declare @route varchar(50), @f_route varchar(50)

select @id=0, @seq_num=0, @route='', @f_route=''

while @id is not null
begin
   select @id=min(id) from tab where id > @id
   while @seq_num is not null
      begin
      select @seq_num=min(seq_num), @route=min(route)from tab where id = @id
      and seq_num > @seq_num
      If @seq_num is null and @id is not null
      insert into #tmp values(@id, @f_route)
      select @f_route = @f_route + case @f_route when '' then '' else ','
end + @route
      end
   select @seq_num=0, @f_route=''
end
select * from #tmp
truncate table #tmp

-- 
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

• Previous message: Shawn: "Odd pivot table type query"
• In reply to: Shawn: "Odd pivot table type query"
• Next in thread: Shawn: "Re: Odd pivot table type query"
• Reply: Shawn: "Re: Odd pivot table type query"
• Messages sorted by: [ date ] [ thread ]