June 20, 2012 at 3:28 pm
Hi I Have distinct values like
1 game1
2 game2
3 game4
I want output in
1 game1 game2 game4
Below is the query for Generating Table
select '1' as ID,'Game1' AS Name
union all
select '2' AS ID,'Game2' AS Name
union all
select '3' AS ID,'Game4' AS Name
June 20, 2012 at 4:00 pm
Hi,
Try:
SELECT SUBSTRING(( SELECT ', ' + CAST(ID as varchar) + ' ' + Name
FROM MyTable
FOR XML PATH('')
), 3, 200000)
Hope this helps.
June 20, 2012 at 4:10 pm
Thanks!! It worked for me
June 20, 2012 at 7:39 pm
Try it with this test data:
select '1' as ID,'Game1' AS Name
union all select '2' AS ID,'Game2' AS Name
union all select '3' AS ID,'Game4' AS Name
union all select '4' AS ID,'Game&' AS Name
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 21, 2012 at 5:17 am
imex (6/20/2012)
Hi,Try:
SELECT SUBSTRING(( SELECT ', ' + CAST(ID as varchar) + ' ' + NameFROM MyTable
FOR XML PATH('')
), 3, 200000)
Hope this helps.
Couple of problems with this.
1) As Dwain stated, what happens when you have non-friendly characters in the data? (e.g. &)
2) Using SUBSTRING is probably the wrong way to go about removing preceding characters - STUFF was designed for the task.
So, let's take Dwain's sample data: -
SELECT ID, Name
INTO MyTable
FROM (SELECT '1', 'Game1'
UNION ALL
SELECT '2', 'Game2'
UNION ALL
SELECT '3', 'Game4'
UNION ALL
SELECT '4', 'Game&') a(ID,Name);
Now run your query against it: -
SELECT SUBSTRING((SELECT ', ' + CAST(ID as varchar) + ' ' + Name
FROM MyTable
FOR XML PATH('')), 3, 200000);
And here's the result
--------------------------------------
1 Game1, 2 Game2, 3 Game4, 4 Game&
OK, we were expecting "Game1, 2 Game2, 3 Game4, 4 Game&", so that was incorrect.
Try this instead -
SELECT STUFF((SELECT ', ' + CAST(ID as varchar) + ' ' + Name
FROM MyTable
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,4,'');
And the result: -
---------------------------------
Game1, 2 Game2, 3 Game4, 4 Game&
Ah ha! Much better! 😀
June 21, 2012 at 5:46 am
Ooooooh! I like this.
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
Much cleaner that what I've been using!
BTW. Your demonstration didn't quite hit the mark about the ampersand because the string was apparently truncated when you SELECTed it INTO the temp table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 21, 2012 at 6:15 am
Cadavre thanks for your solution.. The solution you provided solved some of the conflicts that I'm getting now.. Thanks guys!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply