Bending strings

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

    Based on this, which is an interesting problem: http://www.sqlservercentral.com/Forums/Topic1625170-2799-1.aspx

    Show how to move rows to a delimited string and then summarize.

  • Jeff Moden

    SSC Guru

    Points: 994269

    Steve Jones - SSC Editor (10/13/2014)


    Based on this, which is an interesting problem: http://www.sqlservercentral.com/Forums/Topic1625170-2799-1.aspx

    Show how to move rows to a delimited string and then summarize.

    What would you suggest as a title for a high Google hit rate on something like this?

    If nothing goes haywire at work, I believe I can have a Spackle article on this tonight.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

    Not sure. I wasn't sure how to frame this, but I've seen the concatenation question regularly, and sometimes with some aggregation or summary (like counting responses and listing them).

    Denormalize responses to a poll with T-SQL?

    Summarizing and Displaying All Values in One Row?

    Another way to Pivot data in T-SQL?

    I'm open to suggestions.

  • Jeff Moden

    SSC Guru

    Points: 994269

    Steve Jones - SSC Editor (10/13/2014)


    Not sure. I wasn't sure how to frame this, but I've seen the concatenation question regularly, and sometimes with some aggregation or summary (like counting responses and listing them).

    Denormalize responses to a poll with T-SQL?

    Summarizing and Displaying All Values in One Row?

    Another way to Pivot data in T-SQL?

    I'm open to suggestions.

    K. I'll think of something. You're right... it is a common question. Wayne Sheffield's article on creating CSV's is a great one but I can see the need for something that will cover things like those in the URL you cited.

    p.s. I can't speak for anyone else but it seems like the response time even just to view and article have gotten pretty slow. Clicking on "Quote" to respond to a thread seems to take a huge amount of time.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Jeff Moden

    SSC Guru

    Points: 994269

    I started working on the article last night but didn't finish it. It's coming.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Ed Wagner

    SSC Guru

    Points: 286957

    I think the article will definitely be an interesting one. Also, the page load time has increased significantly for me as well.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

  • Jeff Moden

    SSC Guru

    Points: 994269

    Steve Jones - SSC Editor (1/22/2015)


    bump

    <HeadDesk> My apologies. Major side tracks and I forgot about it. I'll resurrect it this weekend.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Jeff Moden

    SSC Guru

    Points: 994269

    Jeff Moden (1/22/2015)


    Steve Jones - SSC Editor (1/22/2015)


    bump

    <HeadDesk> My apologies. Major side tracks and I forgot about it. I'll resurrect it this weekend.

    Crud. My apologies. Totally lost track of this. I need to see if I can even find what I was working on.

    (Note to self: Nested Concatenation)

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Jeff Moden

    SSC Guru

    Points: 994269

    Jeff Moden - Sunday, February 14, 2016 2:23 PM

    Jeff Moden (1/22/2015)


    Steve Jones - SSC Editor (1/22/2015)


    bump

    <HeadDesk> My apologies. Major side tracks and I forgot about it. I'll resurrect it this weekend.

    Crud. My apologies. Totally lost track of this. I need to see if I can even find what I was working on.(Note to self: Nested Concatenation)

    Jeez... My apologies... I've really dropped the ball for articles.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

    No need to apologize. I know how busy life can get.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

    bump if anyone wants to take this

  • Jeff Moden

    SSC Guru

    Points: 994269

    I look back at this request every once in a while and I also look back at the original request that inspired it and, maybe I'm just tired tonight, but I'm having a difficult time in finding a practical use for such a thing.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • T::Libs

    SSC Enthusiast

    Points: 171

    I have come across this at work several times.

    I found a solution using a custom C# CLR Aggregate function. Interestingly enough, the example MSDN uses for aggregated functions is concatenate:

    https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregate-invoking-functions

    Its fairly simple to implement if you have a version of Visual Studio handy. I modified this to include a custom delimiter. I don't know what MS has not included this function in any SQL build, but there is CONCAT starting with SQL Server 2012

    Also.Β There is also an article here, at sqlservercentral.com, that explains how the above example is implemented.

    https://www.sqlservercentral.com/articles/a-genuine-use-for-a-sql-clr-aggregate

    I find this incredible useful and more straightforward than a CTE.

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

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