SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FOR XML PATH


FOR XML PATH

Author
Message
bc_
bc_
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 7333
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
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5751 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.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 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 Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67565 Visits: 18570
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 (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: 2967 Visits: 208
Good One.
I also used STUFF and FOR XML to solve a problem at my workplace, but was surprised to see &
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26186 Visits: 12503
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 Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 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
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: 4350 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_
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 7333
ARIAL BLACK

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

bc
paul.goldstraw
paul.goldstraw
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1508 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