January 30, 2013 at 9:40 am
I was asked today to look at a colleague's code on concatenating values returned from 1 column. He said that they have always used the method illustrated below:
create table #temp (data varchar(32))
declare @sql varchar(512)
insert #temp
select 'a'
union all
select 'b'
union all
select 'c'
union all
select 'd'
union all
select 'e'
union all
select 'e'
union all
select 'f'
select @sql = ''
select @sql = @sql + data from #temp
select @sql
drop table #temp I understand what is happening here, but believe that this could change at any time in a future version. In my opinion the statement "select @sql = @sql + data from #temp" should fail with an error something like "more than 1 value returned for column data" or something of that nature. Am I wrong?
Jared
CE - Microsoft
January 30, 2013 at 10:00 am
No, no syntax errors per se, but that method's not guaranteed to work safely either.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 30, 2013 at 10:59 am
ScottPletcher (1/30/2013)
No, no syntax errors per se, but that method's not guaranteed to work safely either.
Thanks Scott! Now, in a second question... What do you think is the best way to do the above, but eliminate duplicate values in the final result. i.e. final result would be 'abcdef' instead of 'abcdeef'
We are trying to come up with a good way to do it without a subquery or CTE, but I don't think it an be done without one.
Jared
CE - Microsoft
January 30, 2013 at 11:21 am
SELECT STUFF(( SELECT ''+A.data FROM (SELECT DISTINCT TOP 100 PERCENT data FROM #temp ORDER BY data) A
FOR XML PATH ('')),1,1,'')
Would that not work? I have found that to be effective. I mean, there is a sub query, but really, its pretty darn efficient. I think I have seen timings (cant find them atm though) and it beats using a CTE.
January 30, 2013 at 11:42 am
DiverKas (1/30/2013)
SELECT STUFF(( SELECT ''+A.data FROM (SELECT DISTINCT TOP 100 PERCENT data FROM #temp ORDER BY data) AFOR XML PATH ('')),1,1,'')
Would that not work? I have found that to be effective. I mean, there is a sub query, but really, its pretty darn efficient. I think I have seen timings (cant find them atm though) and it beats using a CTE.
Almost! change the second 1 to a 0 to keep the 'a' in the string.
Jared
CE - Microsoft
January 30, 2013 at 9:11 pm
ScottPletcher (1/30/2013)
No, no syntax errors per se, but that method's not guaranteed to work safely either.
Scott - Can you explain this?
I understand that ordering may be an issue. Anything else?
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
January 30, 2013 at 9:15 pm
SQLKnowItAll (1/30/2013)
DiverKas (1/30/2013)
SELECT STUFF(( SELECT ''+A.data FROM (SELECT DISTINCT TOP 100 PERCENT data FROM #temp ORDER BY data) AFOR XML PATH ('')),1,1,'')
Would that not work? I have found that to be effective. I mean, there is a sub query, but really, its pretty darn efficient. I think I have seen timings (cant find them atm though) and it beats using a CTE.
Almost! change the second 1 to a 0 to keep the 'a' in the string.
That's because there's no need for STUFF:
SELECT TOP 1 (
SELECT DISTINCT data + ''
FROM #temp
FOR XML PATH(''))
FROM #temp
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
January 31, 2013 at 1:35 am
dwain.c (1/30/2013)
ScottPletcher (1/30/2013)
No, no syntax errors per se, but that method's not guaranteed to work safely either.Scott - Can you explain this?
I understand that ordering may be an issue. Anything else?
The same method is used for dynamic crosstabs[/url] explained in a little more detail here[/url]. Until someone posts a problem with it I'm going to assume the method is guaranteed to work because Jeff's articles have a very high read count (nearly 38,000 folks have read the Crosstabs and Pivots article). That's quite a lot of ad hoc testing.
Having said that, I read somewhere on ssc that the method worked by accident rather than by design and may not work in a future release of the product. I'm not holding my breath.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 31, 2013 at 4:44 am
ChrisM@Work (1/31/2013)
dwain.c (1/30/2013)
ScottPletcher (1/30/2013)
No, no syntax errors per se, but that method's not guaranteed to work safely either.Scott - Can you explain this?
I understand that ordering may be an issue. Anything else?
The same method is used for dynamic crosstabs[/url] explained in a little more detail here[/url]. Until someone posts a problem with it I'm going to assume the method is guaranteed to work because Jeff's articles have a very high read count (nearly 38,000 folks have read the Crosstabs and Pivots article). That's quite a lot of ad hoc testing.
Having said that, I read somewhere on ssc that the method worked by accident rather than by design and may not work in a future release of the product. I'm not holding my breath.
Every self-proclaimed SQL guru with a blog has an example of this on their sites. Never have I heard a whisper about issues with it, but then that doesn't say much given the general quality of those sources.
If I were Microsoft, my feeling would be why tinker with something that works. Of course, making it not work could be a side effect of doing something else.
I must confess though to always having my suspicions about it just because it doesn't look like something that was intended as a SQL feature (and I'm not sure why I can say this other than my gut rumbling in disquiet). So far, I haven't been able to not make it work when I've tried to use it. Although I have seen some instabilities related to ordering which I was able to overcome. Actually my biggest issue with it is that it's pretty useless if the concat process has to be applied to split record groups.
Can't wait to hear what Scott might have to say about it.
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
January 31, 2013 at 6:38 am
Here's my issue...
1. It is not syntactically correct, so it should not work. Just because it solves a problem that many of us have does not make it a good thing. It shouldn't even parse.
2. However!!! SQL 2012 solves this problem with new window functions, so I'm not going to complain too much... 😀
Jared
CE - Microsoft
January 31, 2013 at 5:14 pm
SQLKnowItAll (1/31/2013)
Here's my issue......
2. However!!! SQL 2012 solves this problem with new window functions, so I'm not going to complain too much... 😀
Jared - I would be most interested in seeing an example of this as I don't have a SQL 2012 sandbox to play in. 🙂
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
January 31, 2013 at 8:28 pm
dwain.c (1/31/2013)
SQLKnowItAll (1/31/2013)
Here's my issue......
2. However!!! SQL 2012 solves this problem with new window functions, so I'm not going to complain too much... 😀
Jared - I would be most interested in seeing an example of this as I don't have a SQL 2012 sandbox to play in. 🙂
I found out about these on Itzik's site. http://www.sqlmag.com/article/tsql/sql-server-2012-window-functions-140228 I used these new window functions to do running totals and many other things.
Jared
CE - Microsoft
January 31, 2013 at 8:50 pm
SQLKnowItAll (1/31/2013)
dwain.c (1/31/2013)
SQLKnowItAll (1/31/2013)
Here's my issue......
2. However!!! SQL 2012 solves this problem with new window functions, so I'm not going to complain too much... 😀
Jared - I would be most interested in seeing an example of this as I don't have a SQL 2012 sandbox to play in. 🙂
I found out about these on Itzik's site. http://www.sqlmag.com/article/tsql/sql-server-2012-window-functions-140228 I used these new window functions to do running totals and many other things.
Oooooh! And it even includes a link to download SQL 2012! Thanks my friend. Saves me a search.
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
February 1, 2013 at 5:53 am
@Jared, where did you land on the code review? Are you sending the code back for refactoring using officially supported techniques?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2013 at 6:23 am
opc.three (2/1/2013)
@Jared, where did you land on the code review? Are you sending the code back for refactoring using officially supported techniques?
We are going to use the FOR XML method. Mostly because it is cleaner for what we are doing. This is actually just a DBA script for monitoring and we break our own rules all of the time.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply