Post reply

Query to denormalize table rows into single row

  • March 5, 2010 at 4:14 pm

    #212135

    I have a situation where a table has - lets say two columns

    Person name and transaction date.

    A person can have up to 5 transactions (no more than 5)

    So there can be 1 thru 5 records for a given person.

    For some reporting needs I have to denormalize this table such that it

    has just one record for each person with 5 date columns - showing the five dates in ascending order.

    I can do this using cursor logic I was looking for a way wo using cursors -

    Here are sample tables etc.

    create table InTbl (

    nmchar(2),

    dtsmalldatetime

    )

    insert into InTbl values ('AB', '2009-01-01')

    insert into InTbl values ('AB', '2009-02-01')

    insert into InTbl values ('AB', '2009-04-01')

    insert into InTbl values ('AB', '2009-05-01')

    insert into InTbl values ('XY', '2009-01-01')

    insert into InTbl values ('XY', '2009-02-01')

    insert into InTbl values ('XY', '2009-04-01')

    insert into InTbl values ('OO', '2009-01-01')

    insert into InTbl values ('OO', '2009-02-01')

    insert into InTbl values ('OO', '2009-03-01')

    insert into InTbl values ('OO', '2009-04-01')

    insert into InTbl values ('OO', '2009-05-01')

    insert into InTbl values ('JJ', '2009-09-01')

    insert into InTbl values ('DD', '2009-01-01')

    insert into InTbl values ('DD', '2009-11-01')

    I want the output table to look like this.

    OutTbl

    NMDt1Dt2Dt3Dt4Dt5

    AB2009-01-012009-02-012009-04-012009-05-01

    XY2009-01-012009-02-012009-04-01

    OO2009-01-012009-02-012009-03-012009-04-012009-05-01

    JJ2009-09-01

    DD2009-01-012009-11-01

  • March 5, 2010 at 4:23 pm

    #1129499

    Would you please confirm the SQL Server version you're using?

    It's essential for the solution that can be used.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 6, 2010 at 10:37 pm

    #1129713

    SQL Server 2000

    Thanks

  • March 7, 2010 at 1:59 am

    #1129728

    Please read the short discussion in this thread:

    http://www.sqlservercentral.com/Forums/FindPost854349.aspx. It will show you two alternative ways to do it.

    (Side note: I found it by using "ROW_NUMBER 2000" as search terms in the upper right search box in this site... 😉 )


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 7, 2010 at 9:38 pm

    #1129836

    Is it for some kind of report?

    If yes what is the exppected format of the report?

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

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