-
sql query formating
hi all,
i am a student from India having a small problem creating a specific resulting format of a sql query. the details are:
tblPersons
-------------
PersonId | Name
-----------------------
1 | Tom
2 | Joe
3 | Mary
4 | Jim
5 | Harry
tblResponse
--------------
ResponseId | GroupId | Description
------------------------------------------
1 | 1 | Anniversary
2 | 1 | Clambake
3 | 2 | Conference Call
4 | 1 | Birthday
tblResponses
--------------
EventId | PersonId | Response
---------------------------------
1 | 1 | Y
2 | 1 | Y
3 | 1 | Y
4 | 1 | Y
1 | 2 | Y
2 | 2 | N
3 | 2 | Y
4 | 2 | N
1 | 3 | Y
2 | 3 | N
3 | 3 | N
4 | 3 | Y
1 | 5 | Y
2 | 5 | Y
Output for Group 1 (required)
==========================
Event | Tom | Joe | Mary
-------------------------------------
Anniversary | Y | Y | Y
Clambake | Y | N | N
Birthday | Y | N | Y
i wrote join queries which are given below
1. create view temp
as select * from tblresponses
natural join tblpersons
2. create view temp1
as select tblresponses.eventid, groupid, description from tblresponse
inner join tblresponses
on tblresponse.responseid = tblresponses.eventid
where groupid = 1
3. select distinct temp.name, temp.response, temp1.description from temp
natural join temp1
and the resulting table is:
Name | Response | description
---------------------------------------
Tom | Y | Anniversary
Joe | Y | Anniversary
Mary | Y | Anniversary
Tom | Y | Clambake
Joe | N | Clambake
Mary | N | Clambake
Tom | Y | Birthday
Joe | N | Birthday
Mary | Y | Birthday
now i think it is possible with nested queries but don't know how. please help me as it is important for me to present it is that specific format.
thanks
--Anupam|
-
Search for "crosstab query" or "rows to columns" query.
-
:) thankx for the help
yes it works.
thanks again..