Dear Jacob, Nice article, But i am facing problem to run the query what u write here. I got error msg Near XML PATh. Can u tell me what i have to do before run the query. as XML PATH is not working on SSMS windows. thanks
Justin, You can achieve it by using the following trick:
DECLARE @t TABLE (ColorID INT, Color VARCHAR(50)) INSERT INTO @t (ColorID, Color) SELECT 1, 'Soft Pink' INSERT INTO @t (ColorID, Color) SELECT 1, 'Dark Blue' INSERT INTO @t (ColorID, Color) SELECT 2, 'Sky Blue' INSERT INTO @t (ColorID, Color) SELECT 2, 'Ocean Blue'
SELECT ColorID, REPLACE(REPLACE((SELECT REPLACE(Color, ' ', '%20') AS 'data()' FROM @t c2 WHERE c2.ColorID = c1.ColorID FOR XML PATH('')), ' ', '|'), '%20', ' ') AS ColorString FROM @t c1 GROUP BY ColorID
/* ColorID ColorString ----------- -------------------------------------------------- 1 Soft Pink|Dark Blue 2 Sky Blue|Ocean Blue */
Posts
Dear Jacob,
ReplyDeleteNice article,
But i am facing problem to run the query what u write here.
I got error msg Near XML PATh.
Can u tell me what i have to do before run the query.
as XML PATH is not working on SSMS windows.
thanks
Ajeet,
ReplyDeletecould you post the exact error message you are getting?
Also make sure that you are connected to an instance of SQL Server 2005 or above. Run "SELECT @@VERSION" and see what it returns.
I have a rows of color names and sizes which may includes spaces in the name (Soft Pink), when this script runs it makes:
ReplyDeleterow 1: Soft Pink
row 2: Dark Blue
Turn into:
row 1: Soft, Pink, Dark, Blue
it should return:
row 1: Soft Pink, Dark Blue
How can I fix the script to achieve this? Thanks-
Justin,
ReplyDeleteYou can achieve it by using the following trick:
DECLARE @t TABLE (ColorID INT, Color VARCHAR(50))
INSERT INTO @t (ColorID, Color) SELECT 1, 'Soft Pink'
INSERT INTO @t (ColorID, Color) SELECT 1, 'Dark Blue'
INSERT INTO @t (ColorID, Color) SELECT 2, 'Sky Blue'
INSERT INTO @t (ColorID, Color) SELECT 2, 'Ocean Blue'
SELECT ColorID,
REPLACE(REPLACE((SELECT
REPLACE(Color, ' ', '%20') AS 'data()'
FROM @t c2
WHERE c2.ColorID = c1.ColorID
FOR XML PATH('')), ' ', '|'), '%20', ' ') AS ColorString
FROM @t c1
GROUP BY ColorID
/*
ColorID ColorString
----------- --------------------------------------------------
1 Soft Pink|Dark Blue
2 Sky Blue|Ocean Blue
*/
Thanks for your reply, I just recieved it. I did almost the same thing you suggested except I just did instead of this
ReplyDeleteREPLACE(Color, ' ', '%20') AS 'data()'
(Color + '|') AS 'data()'
Does the same thing. Thanks again for your help, this is a wonderful script for what I have to do for 100,000's of records.
Oh yeah, then just replace | with ,!
ReplyDelete