sql query formating
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sql query formating

  1. #1
    Join Date
    Oct 2007
    Posts
    2

    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|
    Last edited by gupta.5882; 10-07-2007 at 04:06 AM.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477
    Search for "crosstab query" or "rows to columns" query.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Oct 2007
    Posts
    2
    thankx for the help
    yes it works.
    thanks again..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width