FOR XML PATH

Question

FOR XML PATH

bc_

Hall of Fame

Points: 3111
More actions
February 18, 2010 at 9:31 pm

#211298

Comments posted to this topic are about the item FOR XML PATH
[font="Arial Narrow"]bc[/font]
February 18, 2010 at 10:34 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nice question.

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

ziangij SSCertifiable Points: 7984 More actions · Answer 2

ziangij

SSCertifiable

Points: 7984

February 19, 2010 at 12:31 am

#1121588

thanks for the question.. good one! 🙂

GlenParker Ten Centuries Points: 1051 More actions · Answer 3

Not sure if it was a browser issue but couldn't copy the script to paste into Query Analyser, anyone else have that problem?

Glen Parker 🙂

simon.whale Ten Centuries Points: 1393 More actions · Answer 4

Great question made me remember to re - read the question a couple of times before shouting "Errggghhhh!!!"

Toreador SSChampion Points: 11358 More actions · Answer 5

GlenParker (2/19/2010)
Not sure if it was a browser issue but couldn't copy the script to paste into Query Analyser, anyone else have that problem?

It's an image rather than text, presumably to prevent people just copying and pasting into SSMS to find the answer - ie to make us think!

I got it right, but only because I was expecting an error that wasn't in the list*, so assumed it must be valid after all 🙂

(* I thought it would object to Colors being in the select list but not the group by)

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 6

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

Good question..I was confused with ans. 1 & 3, went for a toss and lost it. I give too many wrong answeres now a days..Need to be thorough with wot I learn...Questions like this remind me of that fact.

Thanks.

tmacs33 SSC Eights! Points: 975 More actions · Answer 8

I like the question but more importantly I enjoyed the fact that it was an image. Forcing users to think it out.

I was a little thrown off because I thought it would come back with 4 rows. So I assumed the answer meant to say 4 rows of data returned with row 4 = 'black&white,gray'

Great question though. Here comes the debate on why some user couldn't copy the question and then answer it... 😀

bc_ Hall of Fame Points: 3111 More actions · Answer 9

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

(SELECT1, 'Blue'

UNION

SELECT2, 'Red'

UNION

SELECT3, 'Yellow'

UNION

SELECT4, 'Gray'

UNION

SELECT1, 'Purple'

UNION

SELECT2, 'Orange'

UNION

SELECT3, 'Green'

UNION

SELECT4, 'Black & White')

SELECTREPLACE(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]

[font="Arial Narrow"]bc[/font]

SanjayAttray SSChampion Points: 13157 More actions · Answer 10

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 SSCarpal Tunnel Points: 4951 More actions · Answer 11

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

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[/url]
Learn Extended Events

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 13

Good One.

I also used STUFF and FOR XML to solve a problem at my workplace, but was surprised to see &