Forum Replies Created

Viewing 15 posts - 41,521 through 41,535 (of 59,067 total)

  • RE: Concatenating Rows

    john.kilgo (10/14/2009)

    I have no performance comparisons but you could also use the COALESCE function. I pulled this from one of my stored procedures:

    DECLARE @result varchar(max)

    SELECT

    @result = coalesce(@result +...

    --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)

    October 14, 2009 at 7:11 pm

    #1066065

  • RE: Concatenating Rows

    Jeff.Mlakar (10/14/2009)

    What about if you had rows that you wanted to aggregate like this:

    ID NAME

    101 Apple

    101 Banana

    102 Orange

    102 Melon

    102 Grape

    And wanted a result set like:

    ID NAMES

    101...

    --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)

    October 14, 2009 at 12:51 pm

    #1065918

  • RE: Concatenating Rows

    pmcpherson (10/14/2009)

    Fantastic! This forum is making my week! Really! I just have one question now; how do you un-concatenate a string into rows efficiently?

    A decent place to...

    --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)

    October 14, 2009 at 10:54 am

    #1065847

  • RE: Concatenating Rows

    Joel Mansford (10/14/2009)

    In my opinion the 'neatest' solution is to use a custom CLR aggregate function. Afterall this is an aggregate function. Happily MS provide a concatenate as...

    --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)

    October 14, 2009 at 10:45 am

    #1065843

  • RE: Concatenating Rows

    Adam Haines (10/14/2009)

    Actually you dont have replace the invalid characters if you use the FOR XML Clause, with the TYPE directive. You can then use an xquery to extract...

    --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)

    October 14, 2009 at 9:15 am

    #1065762

  • RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

    sdznet (10/14/2009)

    the content is very useful.

    one question-

    is it "somewhat" less complex to produce multi-aggregate crosstabs and pivots using ms-access than currently in sql server?

    If knowledge about MS-Access were gasoline,...

    --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)

    October 14, 2009 at 9:06 am

    #1065753

  • RE: Concatenating Rows

    nicolay.tchernitsky (10/13/2009)

    the XML solution can also be:

    select replace(

    convert(

    varchar(max),

    (select name+',' as [text()] from fruit 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)

    October 14, 2009 at 2:03 am

    #1065525

  • RE: Concatenating Rows

    Nicely written article but even the optimized solution causes a full cartesian product on the far right of the execution plan. That could explain it's performance problem.

    --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)

    October 14, 2009 at 1:56 am

    #1065524

  • RE: SQL CLR code needed to replace fn_regex_split

    Marios Philippopoulos (10/13/2009)

    Jeff Moden (10/10/2009)

    You don't need a CLR for this...

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    Thanks, I will certainly take a close look; I just took a quick glance at your article, and it looks...

    --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)

    October 14, 2009 at 12:16 am

    #1065504

  • RE: Recursive Query Problem

    I noticed you didn't get any replies to your problem. Take a look at the first link in my signature below to see why that might be and what...

    --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)

    October 13, 2009 at 7:38 pm

    #1065470

  • RE: Select 1 day of data

    Thanks for the offer, Seth, but in light of the other posts, there's no need unless you can remember the exception you're thinking of. It also seems to me...

    --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)

    October 12, 2009 at 7:39 pm

    #1064997

  • RE: Reads vs CPU

    Great points, George... thanks.

    --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)

    October 12, 2009 at 6:46 pm

    #1064990

  • RE: SPLITTING CSV COLUMN TO MULTIPLE COLUMNS IN 1 ROW

    Use Lutz's fine example as a derived table and do a join between that and your other column sources. Don't add extra columns to the GROUP BY that are...

    --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)

    October 12, 2009 at 6:39 pm

    #1064988

  • RE: Select 1 day of data

    Garadin (10/12/2009)

    [Edit] Previously suggested a cast method for datecol that as Michael pointed out could prevent index usage. Removing the suggestion so as not to steer people who...

    --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)

    October 12, 2009 at 6:33 pm

    #1064987

  • RE: Help with a Long running stored procedure

    newbie2009 (10/9/2009)

    Jeff, thats exactly it however the execution plan pretty much the same in the dev environment and it runs in 50mins which has been difficult to explain to be...

    --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)

    October 12, 2009 at 6:31 pm

    #1064986

Viewing 15 posts - 41,521 through 41,535 (of 59,067 total)