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


FOR XML PATH


FOR XML PATH

Author
Message
bc_
bc_
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1396 Visits: 7306
Glad most folks liked it. Here is the actual code (you'll have to remove the *'s around amp to make it work):

WITH cteData (ID, Colors) AS
(SELECT 1, 'Blue'
UNION
SELECT 2, 'Red'
UNION
SELECT 3, 'Yellow'
UNION
SELECT 4, 'Gray'
UNION
SELECT 1, 'Purple'
UNION
SELECT 2, 'Orange'
UNION
SELECT 3, 'Green'
UNION
SELECT 4, 'Black & White')

SELECT REPLACE(STUFF((SELECT ', ' + d.Colors
FROM [cteData] d
WHERE c.[ID] = d.[ID]
ORDER BY [d].[Colors]
FOR XML PATH('')),1,1,''), '&*amp*;', '&') [ColorList]
FROM [cteData] c
GROUP BY c.[ID]
ORDER BY c.[ID]



bc
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Good question. The best part was image instead of text. By looking at the question I thought
/* The replace function requires 3 argument(s) */
would be the answer but while writing the same code in SSMS I realized it would return four rows and option 1 should be the answer.

SQL DBA.
LostAccount
LostAccount
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4401 Visits: 1424
Good question. (Maybe because I got it right?). No, actually it was a good question that made me read slowly and think it through.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
paul.goldstraw (2/19/2010)
I liked it like that. It was nice to know that short of copying it letter for letter or using OCR to read the text back, everyone answering should have been answering based on how they understood it rather than because they simply ran the code themselves and put the correct answer

Thanks

Paul


I too liked that the question was done this way. The answers were well formulated and if read - I think they could help deduce the answer quickly. One really needed to read the script and interpret it.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

RakeshRSingh
RakeshRSingh
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2827 Visits: 208
Good One.
I also used STUFF and FOR XML to solve a problem at my workplace, but was surprised to see &
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12001
A good question.

It took me a while to work out because it relies on the fact that ','+d.Colors doesn't have a column name so that for xml path just concatenates the two comma-prefixed colors rather than generating a subelement for each, and I have never used that feature (I've always wanted subelements on the rare occassions I've used for xml) so it didn't click quickly.

Tom

Fatal Exception Error
Fatal Exception Error
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 386
Good Question

I got it right but for the wrong reason's(process of elimination) in my opinion.

I must say I am not a fan of the image since I could not copy the SQL and format it(not run it) to my liking.
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
bc_ (2/19/2010)
Here is the actual code

It is very interesting to see a proportional font (looks like Arial) on the screenshot :-) Do you really use that font for everyday coding instead of a fixed-width font (e.g. Courier New)?
bc_
bc_
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1396 Visits: 7306
ARIAL BLACK

is there an advantage to using a fixed width font? or is it personal preference?

bc
paul.goldstraw
paul.goldstraw
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1272 Visits: 1765
I would say fixed width makes it easier to indent your code since every character fits in a same size block. For me, that's the only reason, it's mere personal preference beyond that. Not sure if others have other reasons why?
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