Post reply

Sql Query Help

  • January 22, 2009 at 6:08 pm

    #93109

    Hi,

    I have a change history table in sql server 2005. The id column are varchar instead of int. Like N123. As it is a change history table, there are number of rows for each id. I need help in writing a query to concatinate all these rows so that I get only one row for each id.

    Say I have my table as:

    ID Description Submitted On UpdatedBy

    N123 This is a test 3/4/2006 AAA

    N123 Test successful 3/5/2006 BBB

    N456 Please verify 4/4/2007 BBB

    N456 Verified 4/5/2007 CCC

    I need a query which will give me a result as:

    ID Description

    N123 This is a test by AAA on 3/4/2006 Test Successful by

    BBB on 3/5/2006

    N456 Please Verify by BBB on 4/4/2007 Verified by CCC on

    4/5/2007

    I then have to map Description field to a text field in different system so that when searched ID N123 the description displays as:

    This is a Test by AAA on 3/4/2006

    Test successful by BBB on 3/5/2006

  • January 22, 2009 at 9:07 pm

    #931615

    This article should give you the info you need.

    http://qa.sqlservercentral.com/articles/Test+Data/61572/

  • January 23, 2009 at 6:28 am

    #931747

    There are three ways you can do this easily. Use TOP, MAX or ROW_NUMBER. I'd recommend testing your system with TOP or ROW_NUMBER because, in my experience, MAX generally doesn't perform as well as the other two. You'd use the date column I assume.

    TOP (untested psuedo-code)

    SELECT ....

    FROM dbo.Table t

    WHERE t.Date = (SELECT TOP(1) t2.Date

    FROM dbo.Table t2

    WHERE t.PK = t2.PK

    ORDER BY t2.Date DESC)

    ROW_NUMBER

    SELECT ....

    FROM (SELECT ....

    ,ROW_NUMBER()OVER (ORDER BY t.Date DESC)

    FROM dbo.Table t

    ) AS x

    WHERE x.RowNum = 1

    In all the testing I've done, ROW_NUMBER is faster on smaller data sets and TOP works better on larger data sets. Your mileage may vary.

    If you can get ahold of it, the last edition of SQL Standard (Nov/Dec 2008) has an article on the topic, including all the code and test results.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 1, 2009 at 8:10 pm

    #936230

    Thank you for your suggestions.

  • February 1, 2009 at 8:45 pm

    #936235

    Eric Klovning (1/22/2009)

    This article should give you the info you need.

    http://qa.sqlservercentral.com/articles/Test+Data/61572/

    Heh... you beat me to it with my own stuff. 🙂

    --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)
    Intro to Tally Tables and Functions

  • February 1, 2009 at 8:46 pm

    #936236

    Grant Fritchey (1/23/2009)

    There are three ways you can do this easily. Use TOP, MAX or ROW_NUMBER.

    I'm thinking this one isn't about how to create the next id. 😉

    --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)
    Intro to Tally Tables and Functions

  • February 2, 2009 at 1:01 am

    #936270

    [font="Verdana"]even almost same discussion can be found at http://qa.sqlservercentral.com/Forums/Topic646567-145-1.aspxBut I think Jeff's article is much better explanatory.

    Mahesh[/font]

    MH-09-AM-8694

  • February 2, 2009 at 4:26 am

    #936332

    Thanks, Mahesh. 🙂

    --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)
    Intro to Tally Tables and Functions

  • February 2, 2009 at 5:55 am

    #936357

    Jeff Moden (2/1/2009)

    Grant Fritchey (1/23/2009)

    There are three ways you can do this easily. Use TOP, MAX or ROW_NUMBER.

    I'm thinking this one isn't about how to create the next id. 😉

    Nah, versioned data. You can use all three. I still prefer MAX becuase I always assume a larger data set that initially specified, but with smaller data sets ROW_NUMBER outperforms the other two. At least in all the tests I ran.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 2, 2009 at 5:13 pm

    #936809

    Grant Fritchey (2/2/2009)

    Jeff Moden (2/1/2009)

    Grant Fritchey (1/23/2009)

    There are three ways you can do this easily. Use TOP, MAX or ROW_NUMBER.

    I'm thinking this one isn't about how to create the next id. 😉

    Nah, versioned data. You can use all three. I still prefer MAX becuase I always assume a larger data set that initially specified, but with smaller data sets ROW_NUMBER outperforms the other two. At least in all the tests I ran.

    Ok... but what does than have to do with concatenation of sequenced 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)
    Intro to Tally Tables and Functions

  • February 3, 2009 at 5:37 am

    #937039

    Jeff Moden (2/2/2009)

    Grant Fritchey (2/2/2009)

    Jeff Moden (2/1/2009)

    Grant Fritchey (1/23/2009)

    There are three ways you can do this easily. Use TOP, MAX or ROW_NUMBER.

    I'm thinking this one isn't about how to create the next id. 😉

    Nah, versioned data. You can use all three. I still prefer MAX becuase I always assume a larger data set that initially specified, but with smaller data sets ROW_NUMBER outperforms the other two. At least in all the tests I ran.

    Ok... but what does than have to do with concatenation of sequenced rows?

    I read that three times and didn't see the word concatenate until now... [putting on the blind fold, lighting a cigarette] Fire the pork chops when ready...

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 3, 2009 at 1:17 pm

    #937493

    You lucky basta*d, I can,t find a single youtube video on the subject... what is the world comming to?? :alien:

  • February 3, 2009 at 7:46 pm

    #937678

    Grant Fritchey (2/3/2009)

    I read that three times and didn't see the word concatenate until now... [putting on the blind fold, lighting a cigarette] Fire the pork chops when ready...

    Heh... nah... you don't need pork chops... you need coffee. 😛

    --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)
    Intro to Tally Tables and Functions

  • March 13, 2009 at 1:29 pm

    #959412

    I am working on SQL 2005

    I need to store values from different fields in a new field. No need to take the column if no value. Something like this:

    col1 col2 col3 col4 col5

    test ----- ----- later Col1: Test Col4:later

    I used update statement with case statement for col5

    update t

    set col5=case when col1='' then'' else 'col1:' + col1 end +char (13)+

    case when col2='' then '' else 'col2:'+col2 end +char (13)+

    case.....

    Result:

    col1: Test

    --

    --

    Col4: Later

    Its seems like it is leaving 2 spaces for col2 and col3

    How do I remove these space?

    expected result is:

    col1: Test

    col4: Later

    I tried Replace() but it is not working

    update t

    set col5=replace (' ','')

    Thanks

  • March 13, 2009 at 1:33 pm

    #959416

    sorry,this was suppose be my new topic. Ignor this added a new topic: How to remove space.

Viewing 15 posts - 1 through 14 (of 14 total)

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