Concatenation Cursor
-
Comments posted to this topic are about the item Concatenation Cursor
-
It's a very good question. Realy useful.
-
thanks, a very good question ! got to learn something new today.:-)
-
I have never been so glad to be wrong! I think some of my SPs with temp cursors may meet an end.
-
An excellent question! Thank you for posting it!
-
This is a statement I use all the time in creating Dynamic SQL strings. It is great for use with pivot tables. Something I have been using for a while.:cool:
Rick Karpel
-
Just remember that without an order by clause, there is no guaranteed order. The correct answer for this could have been just as easily "Bill, Mark, John".
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 -
Wow, I never knew about this! I've got a few SPs to clean up this morning!
-----
a haiku...NULL is not zero
NULL is not an empty string
NULL is the unknown -
This script is very useful when all the rows are not NULL, but if you modify the insert of Mark with NULL, the script only returns Bill.
insert into @table values (4, NULL,'M')
Result: Bill
Where John goes???
-
Nice question. I guessed at the answer but lucked out.
I already have a use for this in a couple of procedures today. PERFECT TIMING!!
-
As a couple of comments stated, the names may not come back in the order you want. On the same server, I ran this query at different times and got "John, Mark, Bill", "John, Bill, Mark" and "Bill, John, Mark" .
-
Juan de Dios (2/16/2010)
--------------------------------------------------------------------------------
This script is very useful when all the rows are not NULL, but if you modify the insert of Mark with NULL, the script only returns Bill.
insert into @table values (4, NULL,'M')
Result: Bill
Where John goes???
stewartc-708166
To cate for this, an aditional clause should be added to the predicate, viz:
AND Name IS NOT NULL
In addition or an alternative to stewartc excellent suggestion, you can in effect negate the presence of the NULL by using an ORDER BY [name] clause
More interesting try the code with a BLANK name, without the ORDER BY clause it appears in the list of names as , ,
- with the ORDER BY clause it appears as a leading comma before Bill.
sanbornd
An EXCELLENT QOD thanks for devising it and submitting, may I encourage you to submit additional QODs
-
Here's another alternative using STUFF and FOR XML PATH that does not require a parameter (I believe this is from an article from SSC. If I could have found the URL I would have included it in this post in order to give the appropriate credit.)
SELECT STUFF((SELECT ', ' + name
FROM @table
WHERE gender = 'M'
FOR XML PATH('')), 1, 2, '')
-
Nice question indeed. Thanks for submitting it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply