June 29, 2011 at 3:23 am
opc.three (6/28/2011)
dbo.GROUP_CONCAT(i.OffID) AS offidlist
Wouldn't that require the sorted variant to be strictly the same as the FOR XML example?
Also, I don't want to discourage you, but that is very much beta code (with plenty of scope for improvement before it could be considered 'finished') so I would not necessarily encourage people to download it and use it without appropriate warnings.
Also, Adam Machanic would no doubt appreciate you attributing the ThreadSafeDictionary code in your project to him. He wrote it and it remains his intellectual property. You should not use it without permission or appear to claim the work as your own.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 29, 2011 at 8:00 am
SQLkiwi (6/29/2011)
opc.three (6/28/2011)
dbo.GROUP_CONCAT(i.OffID) AS offidlistWouldn't that require the sorted variant to be strictly the same as the FOR XML example?
If sorting were a requirement then the dbo.GROUP_CONCAT_S UDA would be the proper choice.
Also, I don't want to discourage you, but that is very much beta code (with plenty of scope for improvement before it could be considered 'finished') so I would not necessarily encourage people to download it and use it without appropriate warnings.
Discourage me, I doubt that's possible. The download page on the site says Beta for that very reason 😀 The site has garnered some downloads, however not much in the way of feedback. With my limited SQLCLR skills I was able to achieve a solution that provides a more concise syntax and comparable performance to the native XML/T-SQL solution and I only think it can improve from there.
Also, Adam Machanic would no doubt appreciate you attributing the ThreadSafeDictionary code in your project to him. He wrote it and it remains his intellectual property. You should not use it without permission or appear to claim the work as your own.
:blush: eeek, is that up there? That is part of the Testing solution/folder. The ThreadSafeDictionary is not part of the downloadable version. I typed the class into the project directly from his book "Expert SQL Server 2005 Development" which is available on Google Books. I am not a great typist so did not bring in any comments, but damn, I should have explicitly attributed the class to him before uploading it. I just did that. I was merely demoing his technique and running it for performance against the other methods that I developed. IIRC it requires changes to the TRUSTWORTHY property of a database and for the CLR assembly to be marked UNSAFE, so I went away from it. Adam's library has received full credit and as I mentioned is only part of the Testing folder of code. It's not included in the Main folder nor is it part of the downloadable solution. All the code in the downloadable solution is from my own development experience, learning and testing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 8:17 am
Fal (6/28/2011)
Ninja's_RGR'us (6/28/2011)
Why the heck are you going recursive with this???Hey, don't get me wrong, I agree with your sentiments exactly. Any form or recursion/looping should be avoided like the plague.
That said, it is still an option albeit one from the bottom of the barrel. The OP may face constraints that prevent the use of better options, and a poor option is better than no option.
Steve.
Thing is, this misses the point of my post in the first place. Heck, I could've written a recursive option. Since SQL involves batch, NOT RBAR (thanks, Jeff Moden :-D), I was looking for a way to NOT do this recursively.
Anytime I post a question to these things, I'm looking for an elegant solution, not just a way to "just get it done."
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 29, 2011 at 8:28 am
So you still need help of you're all set?
June 29, 2011 at 8:32 am
Ninja's_RGR'us (6/29/2011)
So you still need help of you're all set?
Nope, I'm all set.
(Although, for the benefit of others, if anyone else has ideas, feel free to post! :-))
Thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 29, 2011 at 8:38 am
Ray K (6/29/2011)
Ninja's_RGR'us (6/29/2011)
So you still need help of you're all set?Nope, I'm all set.
(Although, for the benefit of others, if anyone else has ideas, feel free to post! :-))
Thanks!
So you went with the non-recursive for xml solution?
June 29, 2011 at 8:42 am
Ninja's_RGR'us (6/29/2011)
Ray K (6/29/2011)
Ninja's_RGR'us (6/29/2011)
So you still need help of you're all set?Nope, I'm all set.
(Although, for the benefit of others, if anyone else has ideas, feel free to post! :-))
Thanks!
So you went with the non-recursive for xml solution?
Yep. I pretty much took what you posted and tweaked it. Works like a champ!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
July 1, 2011 at 8:28 am
mtillman-921105 (6/28/2011)
Ninja's_RGR'us (6/28/2011)
Here's a sample concat code. Enjoy the little tweaks!
SELECTT.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
Thanks Ninja's_RGR'us. I've been using COALESCE for this sort of thing, but this is more straght-forward in a way - I like it. "I like it a lot."
+1 This is a great snippet to put in my library - kudos!
________________________________________________________________________________
Can I ask you a rhetorical question...?
________________________________________________________________________________
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply