Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

FOR XML PATH Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 7:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:28 PM
Points: 1,343, Visits: 7,164
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
Post #869095
Posted Friday, February 19, 2010 8:25 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:58 AM
Points: 3,924, Visits: 1,588
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.
Post #869124
Posted Friday, February 19, 2010 9:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:06 AM
Points: 3,879, Visits: 1,086
Good question. (Maybe because I got it right?). No, actually it was a good question that made me read slowly and think it through.
Post #869213
Posted Friday, February 19, 2010 10:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 20,466, Visits: 14,098
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #869256
Posted Friday, February 19, 2010 9:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:48 AM
Points: 2,819, Visits: 202
Good One.
I also used STUFF and FOR XML to solve a problem at my workplace, but was surprised to see &
Post #869557
Posted Sunday, February 21, 2010 3:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 8,288, Visits: 8,739
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
Post #869948
Posted Tuesday, February 23, 2010 9:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 154, Visits: 381
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.
Post #871212
Posted Thursday, February 25, 2010 1:06 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
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)?
Post #872513
Posted Thursday, February 25, 2010 7:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:28 PM
Points: 1,343, Visits: 7,164
ARIAL BLACK

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


bc
Post #872739
Posted Thursday, February 25, 2010 7:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:19 AM
Points: 940, Visits: 1,281
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?
Post #872749
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse