Concatenation Cursor

Question

Concatenation Cursor

Viewing 15 posts - 16 through 30 (of 42 total)

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 1

A really good QotD! Thanks.

Tom Garth
Vertical Solutions[/url]

"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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 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

brad.green Old Hand Points: 389 More actions · Answer 3

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.

KevinC. SSCommitted Points: 1570 More actions · Answer 4

Excellent question.

And thanks all for the additional information in your posts.

--

Kevin C.

Nick Hart SSC Veteran Points: 279 More actions · Answer 5

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 SSCrazy Points: 2157 More actions · Answer 6

NICE question!

I learnt something new today.:smooooth:

Kelsey Thornton
MBCS CITP

yordan.georgiev SSC Enthusiast Points: 175 More actions · Answer 7

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 Hall of Fame Points: 3196 More actions · Answer 8

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 SSChampion Points: 13729 More actions · Answer 9

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 Valued Member Points: 63 More actions · Answer 10

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 SSCrazy Points: 2563 More actions · Answer 11

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 SSCertifiable Points: 6193 More actions · Answer 12

Its a different look at Quirky Update.

Very elegant in my mind.

Tom Garth
Vertical Solutions[/url]

"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 SSCrazy Eights Points: 8014 More actions · Answer 13

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 SSCrazy Points: 2157 More actions · Answer 14

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

abrazier SSC Enthusiast Points: 112 More actions · Answer 15

abrazier

SSC Enthusiast

Points: 112

February 18, 2010 at 9:52 am

#1121055

Nevermind...