Creating a comma-separated list (SQL Spackle)

Question

Creating a comma-separated list (SQL Spackle)

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

andegre SSCertifiable Points: 5875 More actions · Answer 1

UMG Developer (2/28/2011)
gregory.anderson (2/28/2011)
I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:
For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
And this one:
For XML Path(''), TYPE).Value('.','varchar(max)')
But I don't think those have any bearing on the 'Value' syntax.
I think your problem is that the XML commands are case sensative, and your .Value needs to be .value.

Still a no-go, here's the entire CTE portion of my query:

SelectCTE.CustomerNumber,

CTE.CustomerCredentialID,

CTE.LicenseRestrictionID,

CommentData = Stuff(

(

Select' ' + value

FromIADS..LicenseRestriction lr

Inner Join IADS..License l On l.LicenseID = lr.LicenseID

Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID

And (cc.EndDate IS NULL Or cc.EndDate > @Today)

And cc.CustomerCredentialDispositionID IS NULL

Wherelr.RestrictionCodeID = -8

Andlr.Comment IS NOT NULL

Andcc.CustomerNumber = CTE.CustomerNumber

Andcc.CustomerCredentialID = CTE.CustomerCredentialID

Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID

Order By value

--For XML Path(''), Type).Value('(./text())[1]','varchar(max)')

For XML Path(''), TYPE).value('.','varchar(max)')

,1,1,'')

FromCTE

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

gregory.anderson (2/28/2011)
UMG Developer (2/28/2011)
gregory.anderson (2/28/2011)
I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:
For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
And this one:
For XML Path(''), TYPE).Value('.','varchar(max)')
But I don't think those have any bearing on the 'Value' syntax.
I think your problem is that the XML commands are case sensative, and your .Value needs to be .value.
Still a no-go, here's the entire CTE portion of my query:
SelectCTE.CustomerNumber,
CTE.CustomerCredentialID,
CTE.LicenseRestrictionID,
CommentData = Stuff(
(
Select' ' + value
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Andcc.CustomerNumber = CTE.CustomerNumber
Andcc.CustomerCredentialID = CTE.CustomerCredentialID
Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID
Order By value
--For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
For XML Path(''), TYPE).value('.','varchar(max)')
,1,1,'')
FromCTE

Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.

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

andegre SSCertifiable Points: 5875 More actions · Answer 3

CirquedeSQLeil (2/28/2011)
gregory.anderson (2/28/2011)
UMG Developer (2/28/2011)
gregory.anderson (2/28/2011)
I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:
For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
And this one:
For XML Path(''), TYPE).Value('.','varchar(max)')
But I don't think those have any bearing on the 'Value' syntax.
I think your problem is that the XML commands are case sensative, and your .Value needs to be .value.
Still a no-go, here's the entire CTE portion of my query:
SelectCTE.CustomerNumber,
CTE.CustomerCredentialID,
CTE.LicenseRestrictionID,
CommentData = Stuff(
(
Select' ' + value
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Andcc.CustomerNumber = CTE.CustomerNumber
Andcc.CustomerCredentialID = CTE.CustomerCredentialID
Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID
Order By value
--For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
For XML Path(''), TYPE).value('.','varchar(max)')
,1,1,'')
FromCTE
Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.

Woops, here you go:

Declare@Today DateTime

Set@Today = GetDate()

;WITH CTE As

(

Selectcc.CustomerNumber,

cc.CustomerCredentialID,

lr.LicenseRestrictionID,

lr.Comment

FromIADS..LicenseRestriction lr

Inner Join IADS..License l On l.LicenseID = lr.LicenseID

Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID

And (cc.EndDate IS NULL Or cc.EndDate > @Today)

And cc.CustomerCredentialDispositionID IS NULL

Wherelr.RestrictionCodeID = -8

Andlr.Comment IS NOT NULL

)

SelectCTE.CustomerNumber,

CTE.CustomerCredentialID,

CTE.LicenseRestrictionID,

CommentData = Stuff(

(

Select' ' + value

FromIADS..LicenseRestriction lr

Inner Join IADS..License l On l.LicenseID = lr.LicenseID

Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID

And (cc.EndDate IS NULL Or cc.EndDate > @Today)

And cc.CustomerCredentialDispositionID IS NULL

Wherelr.RestrictionCodeID = -8

Andlr.Comment IS NOT NULL

Andcc.CustomerNumber = CTE.CustomerNumber

Andcc.CustomerCredentialID = CTE.CustomerCredentialID

Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID

Order By value

For XML Path(''), Type).value('(./text())[1]','varchar(max)')

--For XML Path(''), TYPE).value('.','varchar(max)')

,1,1,'')

FromCTE

Order By CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CTE.Comment;

Edit:

My guess is that it's something to do with the ' ' + value portion since I only want the values from the lr.Comment field, but not sure how to specify that in the ' ' + value portion...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

gregory.anderson (2/28/2011)
CirquedeSQLeil (2/28/2011)
gregory.anderson (2/28/2011)
UMG Developer (2/28/2011)
gregory.anderson (2/28/2011)
I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:
For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
And this one:
For XML Path(''), TYPE).Value('.','varchar(max)')
But I don't think those have any bearing on the 'Value' syntax.
I think your problem is that the XML commands are case sensative, and your .Value needs to be .value.
Still a no-go, here's the entire CTE portion of my query:
SelectCTE.CustomerNumber,
CTE.CustomerCredentialID,
CTE.LicenseRestrictionID,
CommentData = Stuff(
(
Select' ' + value
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Andcc.CustomerNumber = CTE.CustomerNumber
Andcc.CustomerCredentialID = CTE.CustomerCredentialID
Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID
Order By value
--For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
For XML Path(''), TYPE).value('.','varchar(max)')
,1,1,'')
FromCTE
Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.
Woops, here you go:
Declare@Today DateTime
Set@Today = GetDate()
;WITH CTE As
(
Selectcc.CustomerNumber,
cc.CustomerCredentialID,
lr.LicenseRestrictionID,
lr.Comment
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
)
SelectCTE.CustomerNumber,
CTE.CustomerCredentialID,
CTE.LicenseRestrictionID,
CommentData = Stuff(
(
Select' ' + value
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Andcc.CustomerNumber = CTE.CustomerNumber
Andcc.CustomerCredentialID = CTE.CustomerCredentialID
Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID
Order By value
For XML Path(''), Type).value('(./text())[1]','varchar(max)')
--For XML Path(''), TYPE).value('.','varchar(max)')
,1,1,'')
FromCTE
Order By CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CTE.Comment;
Edit:
My guess is that it's something to do with the ' ' + value portion since I only want the values from the lr.Comment field, but not sure how to specify that in the ' ' + value portion...

It is choking on the column name of "Value" since you do not have a column named "Value" in your CTE or in your materialized table.

Replace the

Select ' ' + Value

to

Select ' ' + lr.Comment

and change

Order By Value

to

Order By lr.Comment

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

andegre SSCertifiable Points: 5875 More actions · Answer 5

CirquedeSQLeil (2/28/2011)
gregory.anderson (2/28/2011)
CirquedeSQLeil (2/28/2011)
gregory.anderson (2/28/2011)
UMG Developer (2/28/2011)
gregory.anderson (2/28/2011)
I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:
For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
And this one:
For XML Path(''), TYPE).Value('.','varchar(max)')
But I don't think those have any bearing on the 'Value' syntax.
I think your problem is that the XML commands are case sensative, and your .Value needs to be .value.
Still a no-go, here's the entire CTE portion of my query:
SelectCTE.CustomerNumber,
CTE.CustomerCredentialID,
CTE.LicenseRestrictionID,
CommentData = Stuff(
(
Select' ' + value
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Andcc.CustomerNumber = CTE.CustomerNumber
Andcc.CustomerCredentialID = CTE.CustomerCredentialID
Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID
Order By value
--For XML Path(''), Type).Value('(./text())[1]','varchar(max)')
For XML Path(''), TYPE).value('.','varchar(max)')
,1,1,'')
FromCTE
Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.
Woops, here you go:
Declare@Today DateTime
Set@Today = GetDate()
;WITH CTE As
(
Selectcc.CustomerNumber,
cc.CustomerCredentialID,
lr.LicenseRestrictionID,
lr.Comment
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
)
SelectCTE.CustomerNumber,
CTE.CustomerCredentialID,
CTE.LicenseRestrictionID,
CommentData = Stuff(
(
Select' ' + value
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Andcc.CustomerNumber = CTE.CustomerNumber
Andcc.CustomerCredentialID = CTE.CustomerCredentialID
Andlr.LicenseRestrictionID = CTE.LicenseRestrictionID
Order By value
For XML Path(''), Type).value('(./text())[1]','varchar(max)')
--For XML Path(''), TYPE).value('.','varchar(max)')
,1,1,'')
FromCTE
Order By CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CTE.Comment;
Edit:
My guess is that it's something to do with the ' ' + value portion since I only want the values from the lr.Comment field, but not sure how to specify that in the ' ' + value portion...
It is choking on the column name of "Value" since you do not have a column named "Value" in your CTE or in your materialized table.
Replace the
Select ' ' + Value
to
Select ' ' + lr.Comment
and change
Order By Value
to
Order By lr.Comment

I guess that makes sense, but why then does the article state this, but the 'value' works for the scenario in the article?:

Since the field is ',' + Value (an unnamed expression), there is no name for the individual elements. What is left is a list of values, with each value prefixed with a comma. The TYPE clause specifies to return the data as an XML type. The .value('.','varchar(max)') takes each value, and converts it into a varchar(max) data type. The combination of the TYPE and .value means that values are created at XML tags (such as the ampersand (&), and the greater than (>) and less than (<) signs), will not be tokenized into their XML representations and will remain as is.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

That is referring to the actual structure of the xml and not the retrieval of the data.

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

andegre SSCertifiable Points: 5875 More actions · Answer 7

Great! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. 😀

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

gregory.anderson (2/28/2011)
Great! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. 😀

No problem and you are welcome.

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

WayneS SSC Guru Points: 95409 More actions · Answer 9

CirquedeSQLeil (2/28/2011)
gregory.anderson (2/28/2011)
Great! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. 😀
No problem and you are welcome.

Jason, thanks for covering me on this... this thread never popped up on the recent posts, and I didn't see any activity until I got home and could check the email...

Again, thanks!

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

WayneS (2/28/2011)
CirquedeSQLeil (2/28/2011)
gregory.anderson (2/28/2011)
Great! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. 😀
No problem and you are welcome.
Jason, thanks for covering me on this... this thread never popped up on the recent posts, and I didn't see any activity until I got home and could check the email...
Again, thanks!

NP

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

vinaypugalia Old Hand Points: 335 More actions · Answer 11

This link also shares some useful information about generating comma/delimiter separated string.

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post.
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

mister.magoo SSC-Forever Points: 47068 More actions · Answer 12

Sorry: didn't spot how old this was....

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ed.wiebe SSC Rookie Points: 41 More actions · Answer 13

This is great! I have been using the Coalesce function in a udf for this purpose, but this method is faster. I didn't use it as a correlated subquery, but I did replace the internals of my udf with the "For XML PATH" and it took a 3 second query for 10,000 records down to 1 second.

Thank you!

WayneS SSC Guru Points: 95409 More actions · Answer 14

That's great Ed!

... but 1 second is kinda slow for this method on 10,000 rows

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

ed.wiebe SSC Rookie Points: 41 More actions · Answer 15

Yeah well I guess that is more a function of it using views on top of a COTS application database based on two primary tables Component and Relationship. The system is very flexible because you can create any type of component and subsequent relationships between them that you want but it does have a performance cost due to so many joins just to include component properties for instance. The query I am referring to is based on a view that already contains such other included properties and relationships.

The system is for capturing enterprise architecture components and relationships, so luckily the number of objects is relatively small.