January 24, 2005 at 12:14 am
Hi Gurus,
I have a table -- cricket with 2 columns (player varchar(50) and captain varchar(50).
player Captain
Dravid Saurav
Tendul Saurav
Tendul Kapil
Dravid Baji
Now I want a ouput which will look like this :
Saurav
-------
Dravid
Tendul
Kapil
-------
Tendul
Baji
------
Dravid
It will show players under each captain.
How do i write a query which gives me such output ?
Thanks in advance.
--Kishore
January 24, 2005 at 3:18 am
Though SQL is probably not the ideal environment to code this query as you are trying to format data, here is some code that might be want you are after.
/* sample table and data */
create table cricket ( player varchar(50) not null, captain varchar(50) not null)
insert cricket values ( 'Dravid', 'Saurav' )
insert cricket values ( 'Tendul','Saurav' )
insert cricket values ( 'Tendul','Kapil' )
insert cricket values ( 'Dravid','Baji' )
/* display and format data */
select captain, player
from
(
select distinct a.captain , convert(varchar(50),'') as player,
a.captain as link, 11 as captainfirst
from cricket a
union all
select convert(varchar(50),'') as captain, b.player
, b.captain as link, 21 as captainfirst
from cricket b
) captainandplayer
order by link, captainfirst
/* end code */
How does it work?
a) firstly gets a list of the captains
b) secondly gets a list of the players
c) combines these two result sets with a union
d) adds two fields to those result sets to
i) group the captains and his players,
ii) [captainfirst] sort the result, so the captain appears first within a group.
e) returns this as a derived table, so dont have to use temp table.
If you want those '-----' between the captain and his players, add the following :
union all
select distinct convert(varchar(50),'') , convert(varchar(50),'------') as player, captain as link, 15 as captainfirst
from cricket c
union all
select distinct convert(varchar(50),'') , convert(varchar(50),'======') as player, captain as link, 25 as captainfirst
from cricket d
Have fun!
Robert
January 24, 2005 at 5:29 am
This is great work..Thanks Robert.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply