Post reply

Formatting data from two rows

  • June 23, 2009 at 8:12 pm

    #132187

    Hi All.

    I have a SPROC that inserts data into a table in following format.

    LASTNAME FIRSTNAME CITY PHONE STARTDATE ENDDATE

    ----------- ---------- ----- ------- ------------ ---------

    Doe John Houston 2817321432 02/01/09 02/02/09

    Doe John Houston 2817321432 02/03/09 02/06/09

    I am trying to manipulate the data to output in following format onto one row:

    LASTNAME FIRSTNAME CITY PHONE STARTDATE ENDDATE STARDATE2 ENDDATE2

    ---------- ------------ ----- ------ ---------- --------- ---------- ----------

    Doe John Houston 2817321432 02/01/09 02/02/09 02/03/09 02/06/09

    Basically the new row (data) nowhas 2 extra columns STARDATE2 and ENDDATE2. Can someone assist me on how to approach this.

    Thanks.

  • June 23, 2009 at 8:38 pm

    #1014396

    Will there ever be 3 rows?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 23, 2009 at 8:54 pm

    #1014399

    Jeff Moden (6/23/2009)

    Will there ever be 3 rows?

    No, only 2 rows. But why do you ask ?

  • June 23, 2009 at 9:32 pm

    #1014404

    Hi,

    Try this

    Create Table #temp

    (

    slno int identity(1,1),

    LASTNAME varchar(20),

    FIRSTNAME varchar(20),

    CITY varchar(20),

    PHONE varchar(20),

    STARTDATE datetime,

    ENDDATE datetime

    )

    insert into #temp

    select 'Doe','John','Houston','2817321432','02/01/09','02/02/09'

    union all

    select 'Doe','John','Houston','2817321432','02/03/09','02/06/09'

    union all

    select 'Mao','John','Houston','2817321432','02/03/09','02/06/09'

    union all

    select 'Mao','John','Houston','2817321432','02/03/09','02/06/09'

    select LASTNAME,FIRSTNAME,CITY,PHONE,

    min(STARTDATE)STARTDATE,

    min(ENDDATE)ENDDATE,

    max(STARTDATE)STARTDATE2,

    max(ENDDATE)ENDDATE2

    from #temp

    group by LASTNAME,FIRSTNAME,CITY,PHONE

    ARUN SAS

  • June 24, 2009 at 11:28 am

    #1014801

    Arun.

    Thanks. I tried your method. Did not work for my data.

  • June 24, 2009 at 11:33 am

    #1014809

    What "didn't work"?

    The group by is what I'd be looking to do as well.

    You could self join as well. Is there a primary key field?

  • June 24, 2009 at 1:43 pm

    #1014900

    NewBie (6/24/2009)

    Arun.

    Thanks. I tried your method. Did not work for my data.

    Then it looks like you need to provide better details and sample data. See the link in my signature for help in doing so.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 24, 2009 at 1:56 pm

    #1014911

    WayneS (6/24/2009)

    NewBie (6/24/2009)

    Arun.

    Thanks. I tried your method. Did not work for my data.

    Then it looks like you need to provide better details and sample data. See the link in my signature for help in doing so.

    Will do. Thanks.

  • June 27, 2009 at 8:23 pm

    #1016462

    NewBie (6/23/2009)

    Jeff Moden (6/23/2009)

    Will there ever be 3 rows?

    No, only 2 rows. But why do you ask ?

    Just to be sure. Problems like this usually fail sometime in the future because someone drops more than 2 rows into a database despite the problem definition.

    In light of you answer that the code failed for your data, I'd recommend that you post a little more information than the fact that it didn't work with your data. I'd also recommend posting some code in the format the article that's been recommended stipulates. Frequently, when such etiquette is followed, a very well test reply is produced for you in very short order.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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