Re: Odd pivot table type query
From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 11/23/04
- 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 ]
Relevant Pages
|
|
