Post reply

Rows to Columns

  • October 25, 2005 at 1:48 am

    #169556

    I've seen this come up a few times, but couldn't find it after a brief search.  If anyone has any appropriate links saved, that would be great.

    I have a table 'Person' (PersonID, Name, [...])

    and a table 'Payment' (PaymentID, PersonID, PaymentDate, Amount)

    and I want to create a view of each person's last 5 payments (ie in descending PaymentDate order).  Each person will have at least one payment, but may have more or less than 5.  If they have less than 5, I would like NULLs to appear in the appropriate columns.

    So the desired view looks like this:

    PersonID, Name, PaymentDate(1), Amount (1), PaymentDate(2), Amount (2), PaymentDate(3), Amount (3), PaymentDate(4), Amount (4), PaymentDate(5), Amount (5)

    There are not thousands of records involved, so performance is not (and will not become) a big issue.

    Any ideas most welcome - thanks.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 25, 2005 at 6:04 am

    #599755

    Phil - you could always use the "search" (on the menu bar) to look through the "discussions", "articles" & "scripts" - here're a couple of links it came back with when I did a search for "rows to columns"...

    converting rows to columns

    creating rows from columns







    **ASCII stupid question, get a stupid ANSI !!!**

  • October 25, 2005 at 6:49 am

    #599770

    Thanks Sushila.  I think that my main difficulty here is that I need the ranking (1-5 date desc) built in to the query and I cannot see how to do this using CASE statements.  Also, as I would like to have this all in a view, I do not have the luxury of building a stored proc to do it.

    I will just have to change my approach if I cannot find a way to do this ... more work, but c'est la vie.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply