Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenation Cursor


Concatenation Cursor

Author
Message
Nick Hart
Nick Hart
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 365
bitbucket-25253 (2/16/2010)

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


If you used this in the where clause AND ISNULL([name],'') <> '' you will negate both of the issues above.

A very good QOD which I have used a couple of times, but should be able to use more often
Kelsey Thornton
Kelsey Thornton
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 282
NICE question!

I learnt something new today.Smooooth

Kelsey Thornton
MBCS CITP
yordan.georgiev
yordan.georgiev
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 47
declare @table table (id int, [name] varchar(10), gender char(1))
declare @MaleNames table (id int identity(1,1), [name] varchar(10))

insert into @table values (1,'John','M')
insert into @table values (2,'Sue','F')
insert into @table values (3,'Jane','F')
insert into @table values (4,'Mark','M')
insert into @table values (5,'Bill','M')

insert into @MaleNames SELECT [name] from @table where gender = 'M'

SELECT * FROM @MaleNames
James Horsley
James Horsley
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 451
A variation on this that is really useful for dynamic SQL generation is getting a column list for a table - I use variations of the code below (variations include excluding identity columns for inserts to an identity table):


SET @Cols = ''
SELECT
@Cols = @Cols + CASE WHEN ORDINAL_POSITION = 1 THEN '' ELSE ',' END + COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG=@tableCatalog
AND TABLE_SCHEMA=@tableSchema
AND TABLE_NAME=@tableName






James Horsley
Workflow Consulting Limited
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2953 Visits: 3889
brad.green (2/16/2010)
In addition to the comment about the missing order by, the result of this is also dependent on the CONCAT_NULL_YIELDS_NULL option. If this option is OFF, the argument to IsNull() will NOT be null, and therefore the result would be: ", John, Mark, Bill" -- leading comma.

Good point! Was about to "complain" as well, but then saw you already caught this.

The settings-independend version would be something like this:
SELECT @MaleNames = 
CASE WHEN @MaleNames IS NULL THEN '' ELSE @MaleNames + ', ' END + [name]
FROM @table
WHERE gender = 'M'



Best Regards,

Chris Büttner
rodger.shutt
rodger.shutt
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 54
I thought the answer would be the 3 mens names but when I ran it it returned null several times on a sql 2005 server? I then reset all the query options to default and connected to another server and it ran correctly there, so I tried it back on the original server it now works there too so i guess some settings options in the query had resulted in the null result.
Clive Chinery
Clive Chinery
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 154
I tried the following:
declare @table table (id int, [name] varchar(10), gender char(1))
declare @MaleNames varchar(255)

insert into @table values (1,'John','M')
insert into @table values (2,'Sue','F')
insert into @table values (3,'Jane','F')
insert into @table values (4,'Mark','M')
insert into @table values (5,'Bill','M')
insert into @table values (6, null,'M')
insert into @table values (7,'Dingbat','M')

select @MaleNames = IsNull(@MaleNames + ', ','') + [name] from @table where gender = 'M' ORDER BY name

Select @MaleNames as MalePatients

Output is:
Bill, Dingbat, John, Mark
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 1499
Its a different look at Quirky Update.

Very elegant in my mind.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

SQLBOT
SQLBOT
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 836
I usually use COALESCE but ISNULL is easier to spell.

Thanks!

Good question, btw. lots of people haven't seen this trick and it's incredibly useful for generating D-SQL and executing it.

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Kelsey Thornton
Kelsey Thornton
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 282
SQLBOT (2/17/2010)
I usually use COALESCE but ISNULL is easier to spell.


Hehe LOL!


(feels a strange urge to go hide behind the sofa...)

Kelsey Thornton
MBCS CITP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search