Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006

    Exclamation Unanswered: how to display linked table data in columns

    Hello, I am trying to figure out how to write a query to retrieve information from a linked table in columns instead of rows. example:

    Table 1:
    index, name, property
    _1_, aaaaaa, xxxxxx
    _2_, bbbbbb, yyyyyy

    Table 2:
    index, date, value
    _1_, 1/1/1, XXXX
    _1_, 2/2/2, YYYY
    _1_, 3/3/3, ZZZZ
    _2_, 3/3/3, SSSS
    _2_, 4/4/4, TTTT

    Table 1 is linked to Table 2 by index. The results need to look like the following:

    index, name, property, date1, value1, date2, value2, date3, value3
    _1_, aaaaaa, xxxxxx, 1/1/1, XXXX, 2/2/2, YYYY, 3/3/3, ZZZZ
    _2_, bbbbbb, yyyyyy, 3/3/3, SSSS, 4/4/4, TTTT, [NULL], [NULL]

    I could not figure out how to write a query. if you have a perl script or other ways of doing this that would be sufficient as well. thanks again. K
    Last edited by le0davinci; 05-08-06 at 17:13.

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    "rotating" your data like this is also known as building a crosstab query, or a pivot table, and has been discussed many times - I would suggest doing a site search with these terms.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    May 2006

    still no luck

    Most of the solutions I found were related to rotating data in an existing table. some where only rotating a single value. to be a bit more specific I have a table with multiple children, each child has multiple evaluations, each evaluation has multiple questions that have answers each question can be associated with a specific domain. the data I need to display is similar to the following.

    Child1, dob, eval1.domain1.value1, eval1.domain1.value2, eval1.domain1.value3, eval1.domain2.value1, eval1.domain2.value2, eval1.domain2.value3, eval2.domain1.value1, etc....

    the number of evals can be different. some children my have one eval and some may have eight.

    it seems to me that a pl/pgsql script may work but I was hoping for some direction to an example that can help me write such script. Idealy, i'd like to just do it in the query instead. Thanks again for your help.

Posting Permissions

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