Odd pivot table type query

I've been unable to find a way to write the following
query.
Assuming this table:
Year Quarter
-- --
1990 1
1990 2
1990 3
1990 4
1991 1
1991 2
1991 3
1991 4
Does anyone know how to write an SQL query to generate the
following results?
Year Quarter
-- --
1990 1,2,3,4
1991 1,2,3,4
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
|||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
>
>.
>
|||Shawn,
There is no function as such which will do the things for you.
You may try following approach using User defined function. But remember,
this is not a documented method, so it can not be reliable under all
cirumstances.
CREATE FUNCTION EmpPhones (@.ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.str VARCHAR(1000)
SELECT @.str = ISNULL(@.str + ',', '') + cats
FROM tab WHERE id = @.ID
RETURN (@.str)
END
-- sample data / result set.
if object_id ('tab') is not null
drop table tab
go
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'1-001')
insert into tab values(1 ,'2-002')
insert into tab values(1 ,'3-003')
insert into tab values(2 ,'1-011')
insert into tab values(3 ,'1-012')
insert into tab values(3 ,'2-022')
go
--And then you would call this UDF from within a SELECT statement, as
follows:
select distinct id,dbo.empphones(id) 'comma seperated value' from tab
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com