This is not possible with the pure SQL statement you will have to use T-SQL
to achieve this.
See following example of generating a crosstab output.

Ex 1:

if object_id('tab') is not null
drop table tab

create table tab(ID int,
SEQ_NUM int ,
ROUTE varchar(50))
go
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
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

Ex 2:

--Crosstab query.

--Eg:

drop table tab
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'1')
insert into tab values(1 ,'2')
insert into tab values(1 ,'3')
insert into tab values(2 ,'1')
insert into tab values(3 ,'1')
insert into tab values(3 ,'2')
go
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @id int, @old_id int
declare @seq_num int
declare @cats varchar(50), @f_cats varchar(50)
select @id=0, @old_id=0,@seq_num=0, @cats='', @f_cats=''

declare c1 cursor for
select id, cats from tab order by id

open c1

fetch c1 into @id,@cats

while @@fetch_status = 0
begin

If @old_id <> @id and @old_id <> 0
begin
insert into #tmp values(@old_id, @f_cats)
select @seq_num=0, @f_cats=''
end
select @f_cats = @f_cats + case @f_cats when '' then '' else ',' end + @cats
select @old_id = @id
fetch c1 into @id,@cats
end
close c1
deallocate c1
insert into #tmp values(@old_id, @f_cats)
select * from #tmp

-Vishal


"msg_2222@yahoo.com" <u352142333@spawnkill.ip-mobilphone.net> wrote in
message news:l.1060808093.1598602294@[63.127.215.130]...
[quoted text, click to view]

Could someone give two SQL examples that generate
cr reports like the followings:

(1)
Country City count(City)
A AA 3
A AB 2
A AC 5
B BA 3
B BB 1


(2)

Country City countMin City countMed City countMax
A AA 2 AB 3 AC 5
B BA 1 BB 3





--
Sent by msg_2222 from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.