March 21, 2011 at 12:42 pm
Using XML to make a CSV in this way is like cracking a nut with a death star.
March 21, 2011 at 1:22 pm
stefan.hoffmann (3/21/2011)
hi Hugo,I'd like to disagree. While the last example uses only @var = column the explanation above it clearly states:
"If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in the following batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned, which is 1:"
"references a nonscalar expression" should describe exactly our SELECT @var = @var + column FROM table ORDER BY columnOrExpression scenario.
Or is there any subtle interpretation I don't get here, maybe my English is not sufficient enough...
Hi Stefan,
I must admit that it is not as clear-cut as I thought it was. I think I remember that there was an explicit disclaimer for the @var = @var + col case, but I can't find it anymore - maybe I am confisunng this with the equally dangerous SET @var = col = @var + othercol extension to the UPDATE statement.
Basically, if you take the wording very literally, then yes you are right: the variable should be set to the expression in the last row. And if the column value happens to be 'A' for the last row, then the value of @var after SELECT @var = @var + col FROM SomeTable should be set to @var + 'A'. But how does this help us define the expected output? What value for @var has to be used by SQL Server when processing this last row? The value that resulted from the second to last row? The value @var had before the query was started? Or even something else (imagine a parallel plan that uses three processors to process the three partitions of partitioned table SomeTable).
Anyway, I did find another useful link - a Microsoft Knowledge Base article that excplicitly warns that the results of this kind of queries ("aggregate concatenation queries") are undefined - see especially the first sentence after the "Cause" heading: "The correct behavior for an aggregate concatenation query is undefined."
March 21, 2011 at 2:47 pm
That's a gem that's probably well hidden in the documentation. Thanks for pointing that out! Although for really small reference tables (which the OP was saying he wanted to turn into a CSV), the plan would only be single-threaded anyway.
This sounds there may be a case for a MAXDOP query hint if you've got thousands and thousands of rows.. but I'm waiting for someone like The Modenator to descend and slap you about for wanting to do "presentation-level" stuff in the Db 😉
March 21, 2011 at 11:58 pm
Remove last comma or dilimiter without substring ...
Check it out here at www.sqlsuperfast.com
March 22, 2011 at 2:56 am
In the sample of the article with the init of @MyStatusList='' the test ISNULL(@MyStatusList,'') is not necessary:
SET @MyStatusList = ''
SELECT @MyStatusList = ISNULL(@MyStatusList,'') + StatusDesc + ',' FROM MyStatus
Best version, without drop the trailing comma at the end is:
SELECT @MyStatusList = COALESCE(@MyStatusList + ',', '') + StatusDesc FROM MyStatus
March 22, 2011 at 3:05 am
A useful article and discussion. However I do not understand the point of [font="Courier New"]ISNULL(@MyStatusList,'')[/font] in the method given in the article. As the first line of code is SET @MyStatusList = '' [font="Courier New"]@MyStatusList[/font] is never NULL.
Similarly, in Mansfield's suggestion above, the preceding line would have to be SET @MyStatusList for the [font="Courier New"]ISNULL[/font] to work.
What have I missed?
March 22, 2011 at 3:44 am
I think these are the articles describing the problem with this technique being non-deterministic. Complete with examples that may or may not work depending upon your optimiser, the version of mssql you're running and the current phase of the moon.
http://blog.sqlauthority.com/2009/09/29/sql-server-interesting-observation-execution-plan-and-results-of-aggregate-concatenation-queries/[/url]
http://blog.sqlauthority.com/2009/09/20/sql-server-execution-plan-and-results-of-aggregate-concatenation-queries-depend-upon-expression-location/[/url]
March 22, 2011 at 3:47 am
hi David,
in the article the varaibale is initialized with '' - an empty string. This is not necessary. When declaring a variable with out extra value assignment, it gets initialized automatically with NULL. This is where COALESCE(@var + ', ', '') OR ISNULL(@var + ', ', '') does its magic:
The first call to COALESCE/ISNULL is COALESCE(NULL + ', ', ''), which gets evaluated to COALESCE(NULL, '') and this returns ''.
The second and all subsequent calls are like COALESCE('value' + ', ', ''), which returns 'value, '. So you only append the comma in the case when you append a value to an existing value. This means that no leading or trailing comma exists, which must be removed after concatenating the values.
USE AdventureWorksLT ;
GO
-- Initialized with empty string
DECLARE @tablenames NVARCHAR(MAX)= '' ;
SELECT @tablenames = @tablenames + ', ' + name
FROM sys.tables ;
SELECT @tablenames ;
SELECT STUFF(@tablenames, 1, 2, '') ;
-- Initialized automatically with NULL
DECLARE @tablenames2 NVARCHAR(MAX) ;
SELECT @tablenames2 = COALESCE(@tablenames2 + ', ', '') + name
FROM sys.tables ;
SELECT @tablenames2 ;
March 22, 2011 at 3:53 am
Hi David,
You don't have loose nothing.
There is only a redundant test in the second line of the article.
The first line of code is:
SET @MyStatusList = '' so @MyStatusList is never NULL.
so in the second line of code ISNULL(@MyStatusList,'') is redundant because is always false.
If you not set a variable default value is NULL and good version should be:
SELECT @MyStatusList = COALESCE(@MyStatusList + ',', '') + StatusDesc FROM MyStatus
or (is the same)
SELECT @MyStatusList = ISNULL(@MyStatusList + ',','') + StatusDesc FROM MyStatus
March 22, 2011 at 5:19 am
You can also use this one...
DECLARE @MyStatusList VARCHAR(1000)
SET @MyStatusList = ''
SELECT @MyStatusList = @MyStatusList + CASE WHEN @MyStatusList = '' THEN '' ELSE ', ' END + StatusDesc FROM MyStatus
SELECT @MyStatusList
March 22, 2011 at 10:21 am
Thanks for the article. I have used the For XML approach as well as the Coalesce approach in the past. I also recently blogged about the two methods, here[/url].
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2012 at 1:29 am
When using the FOR XML method described in the post by feeza_ibrahim (third post on page 1 of this topic) make sure the column does not contain other than preceding or trailing spaces as in the REPLACE all such spaces are replaced by a separator.
e.g. the Status 'On Hold' will return two values 'On' and 'Hold' in the CSV-string
September 28, 2012 at 1:34 am
Hi All,
Can anyone please elaborate on how it's concatenating the row data in single column...Sorry if it's a basic question but i'll be highly obliged if any one could explain...:-)
SET @MyStatusList = SUBSTRING(@MyStatusList, 1, LEN(@MyStatusList)-1)
September 28, 2012 at 2:22 am
Nobody in this thread seems to have mentioned this alternative technique. It is not as fast as the XML technique but it is very simple, and can be elaborated for some very handy manipulations such as a pivot, or putting delimited lists in markup. All the techniques except the XML one work safest with table variables.
[font="Courier New"]
SELECT @String=REPLACE(@String,'%',Value+',%') FROM YourTable
[/font]
as in...
[font="Courier New"]DECLARE @string VARCHAR(8000)
SELECT @string='%' --or whatever you choose for your marker
SELECT @String=REPLACE(@String,'%',number+',%')
FROM (SELECT 'un' AS [number]
UNION ALL SELECT 'dau'
UNION ALL SELECT 'tri'
UNION ALL SELECT 'pedwar'
UNION ALL SELECT 'pump'
UNION ALL SELECT 'chwech'
UNION ALL SELECT 'saith'
UNION ALL SELECT 'wyth'
UNION ALL SELECT 'naw'
UNION ALL SELECT 'deg') [welsh numbers]
SELECT REPLACE(@String,',%','')
[/font]
Giving ...
------------------------------------------------------------
un,dau,tri,pedwar,pump,chwech,saith,wyth,naw,deg
(1 row(s) affected)
Best wishes,
Phil Factor
September 28, 2012 at 2:39 am
I thought it looked good and tried to replace an existing query with this method, but my original method to 0 seconds and this method needed 41 seconds.
Original method :
CREATE FUNCTION [dbo].[AgentsDropDown]
(
@Exclude varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @List varchar(max)
SELECT @List = '<select name="AgentsList" class="formboxes" onchange="" Multiple ><option value="0">NO AGENT</option>'
+ STUFF(g.y, 1, 0, '')
+ '</select>'
FROM (SELECT DISTINCT CorporateKey as "@value",Name AS "data()"
FROM dbo.Names WHERE
(CHARINDEX(',', dbo.Names.Name) > 0) AND (LEN(dbo.Names.CorporateKey) = 6)
OR(LEN(dbo.Names.CorporateKey) = 10)
AND CorporateKey NOT IN (@Exclude)
ORDER BY Name
FOR XML PATH('option')) AS g(y)
RETURN @List
END
New method:
CREATE FUNCTION [dbo].[StatusList]
(
@Exclude varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @List varchar(max)
SET @List = '<select name="AgentsList" class="formboxes" onchange="" Multiple ><option value="0">NO AGENT</option>'
SELECT @List = ISNULL(@List,'') + '<option value="' + [CorporateKey] + '">' + [Name] + '</option>'
FROM (SELECT DISTINCT TOP 100 percent CorporateKey ,Name
FROM dbo.Names WHERE
(CHARINDEX(',', dbo.Names.Name) > 0) AND (LEN(dbo.Names.CorporateKey) = 6)
OR(LEN(dbo.Names.CorporateKey) = 10)
AND CorporateKey NOT IN (@Exclude)
ORDER BY Name
) AS x
RETURN @List
END
Viewing 15 posts - 46 through 60 (of 87 total)
You must be logged in to reply to this topic. Login to reply