Re: Odd pivot table type query
From: Shawn (shawngerty_at_hotmail.com)
Date: 11/23/04
- Next message: cmaso: ""Length" in table design view"
- Previous message: Vishal Parkar: "Re: Odd pivot table type query"
- In reply to: Vishal Parkar: "Re: Odd pivot table type query"
- Next in thread: Vishal Parkar: "Re: Odd pivot table type query"
- Reply: Vishal Parkar: "Re: Odd pivot table type query"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Nov 2004 17:09:50 -0800
Thanks for the response Vishal. Unfortunatly this
solution isn't going to work for me since I dealing with a
very large number of records the performace of populating
a new table is going to be too great.
I'm adding a "download" feature for the data so this would
result is a large number of records being retuned and
written to file.
What would be great is is I could use somethine like a
GROUP BY and SUM where the SUM would append strings
together.
SELECT year, APPEND(Quarter)
FROM mytable
GROUP BY year
>-----Original Message-----
>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
>
>
>.
>
- Next message: cmaso: ""Length" in table design view"
- Previous message: Vishal Parkar: "Re: Odd pivot table type query"
- In reply to: Vishal Parkar: "Re: Odd pivot table type query"
- Next in thread: Vishal Parkar: "Re: Odd pivot table type query"
- Reply: Vishal Parkar: "Re: Odd pivot table type query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
