Bending strings

Question

Bending strings

Steve Jones - SSC Editor

SSC Guru

Points: 736291
More actions
October 13, 2014 at 9:01 am

#312178

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.

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

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

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.

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)

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 2

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: 1004378 More actions · Answer 3

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.

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)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

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.

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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 6

Steve Jones - SSC Editor

SSC Guru

Points: 736291

January 22, 2015 at 11:18 am

#1772341

bump

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

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.

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)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

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.

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)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 9

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.

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)

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 10

Steve Jones - SSC Editor

SSC Guru

Points: 736291

December 4, 2017 at 11:27 am

#1970578

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

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 11

Steve Jones - SSC Editor

SSC Guru

Points: 736291

March 21, 2018 at 4:27 pm

#1984207

bump

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 12

Steve Jones - SSC Editor

SSC Guru

Points: 736291

August 28, 2018 at 11:40 am

#2003676

bump if anyone wants to take this

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 13

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.

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)

Antony Liberato SSC Enthusiast Points: 172 More actions · Answer 14

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.