June 19, 2017 at 9:42 am
Hi
I stumbled across some SQL code which seems to transpose record data into a single record. Unfortunately the code was supplied "as is" without a useful explanation of how it works. Can anyone please explain how this works? I am completely stumped. Just run the following code which will create a table for you:
1) How do the rows of data become a single row
2) Why is there no comma at the end of the data returned?
DECLARE @TestData TABLE (
[Name] VARCHAR(255)
)
INSERT INTO @TestData([Name]) VALUES ('charlie')
INSERT INTO @TestData([Name]) VALUES ('samanther')
INSERT INTO @TestData([Name]) VALUES ('emma')
INSERT INTO @TestData([Name]) VALUES ('gavin')
DECLARE @DynamicColumns AS VARCHAR(MAX)
SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ','') + QUOTENAME([Name])
FROM
(
SELECT DISTINCT [Name]
FROM @TestData AS [X]
) AS [Products]
SELECT @DynamicColumns
The result set returned is a single record containing:[charlie], [emma], [gavin], [samanther]
Thanks in advance!
Rob
June 19, 2017 at 9:51 am
r.gall - Monday, June 19, 2017 9:42 AMHiI stumbled across some SQL code which seems to transpose record data into a single record. Unfortunately the code was supplied "as is" without a useful explanation of how it works. Can anyone please explain how this works? I am completely stumped. Just run the following code which will create a table for you:
1) How do the rows of data become a single row
2) Why is there no comma at the end of the data returned?
DECLARE @TestData TABLE (
[Name] VARCHAR(255)
)
INSERT INTO @TestData([Name]) VALUES ('charlie')
INSERT INTO @TestData([Name]) VALUES ('samanther')
INSERT INTO @TestData([Name]) VALUES ('emma')
INSERT INTO @TestData([Name]) VALUES ('gavin')DECLARE @DynamicColumns AS VARCHAR(MAX)
SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ','') + QUOTENAME([Name])
FROM
(
SELECT DISTINCT [Name]
FROM @TestData AS [X]
) AS [Products]
SELECT @DynamicColumnsThe result set returned is a single record containing:
[charlie], [emma], [gavin], [samanther]Thanks in advance!
Rob
SQL Server, even when we tell him to work with sets, reads each row one by one. Basically, for each row it's assigning the value of the variable plus a comma plus the value of the row. Please note, that the order of the concatenation is not guaranteed in this code and a better approach would be the one shown on this article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
June 19, 2017 at 9:55 am
r.gall - Monday, June 19, 2017 9:42 AM1) How do the rows of data become a single row
You concatenate the values one at a time into a scalar variable. With no ORDER BY in your SELECT statement, you're not guaranteed to get the elements in any particular order - that may or may not matter to you.
2) Why is there no comma at the end of the data returned?
It's all in the COALESCE. If the variable is NULL, which it is at the start, then the variable plus ',' is also NULL, so that's converted to a blank.
John
June 19, 2017 at 10:00 am
It is a horrible technique but gets the job done. It loops through each row and concatenates it to the variable. A simplified line of code would be SET @v-2 = @v-2 + item;
Be careful because the construct can give you an infinite loop if coded wrong like the next line:
SELECT @v-2 = (select col from tab) -- if there are no results then @v-2 will not get set or changed
Use this:
SET @v-2 = (select col from tab); -- @v-2 will always get set
June 20, 2017 at 10:54 am
Bill Talada - Monday, June 19, 2017 10:00 AMIt is a horrible technique but gets the job done. It loops through each row and concatenates it to the variable. A simplified line of code would be SET @v-2 = @v-2 + item;Be careful because the construct can give you an infinite loop if coded wrong like the next line:
SELECT @v-2 = (select col from tab) -- if there are no results then @v-2 will not get set or changedUse this:
SET @v-2 = (select col from tab); -- @v-2 will always get set
Could you please explain the infinite loop on the SELECT if no results. I thought, as of v2005 all declared variables are initialized to NULL and as such your SELECT @v-2=(select col from tab) would return NULL
June 20, 2017 at 11:09 am
Smendle - Tuesday, June 20, 2017 10:54 AMBill Talada - Monday, June 19, 2017 10:00 AMIt is a horrible technique but gets the job done. It loops through each row and concatenates it to the variable. A simplified line of code would be SET @v-2 = @v-2 + item;Be careful because the construct can give you an infinite loop if coded wrong like the next line:
SELECT @v-2 = (select col from tab) -- if there are no results then @v-2 will not get set or changedUse this:
SET @v-2 = (select col from tab); -- @v-2 will always get setCould you please explain the infinite loop on the SELECT if no results. I thought, as of v2005 all declared variables are initialized to NULL and as such your SELECT @v-2=(select col from tab) would return NULL
If all you have is a DECLARE and a SELECT to assign a value to the variable, that is true.
The sort of thing Bill's talking about could happen when that method of variable assignment is combined with, say, a WHILE @variable IS NOT NULL loop.
The 'SELECT @variable=column FROM table' form does no assignment at all if the result set is empty, i.e., the variable retains its current value. That is different than the 'SET @variable=(SELECT...' form, in which an empty result set for the SELECT will actually assign NULL to the variable (you can also get the assign-a-NULL behavior by doing 'SELECT @variable=(SELECT...')
So, if you have a WHILE @variable IS NOT NULL loop, and you use the 'SELECT @variable=column FROM table.' form of assignment, if you hit a condition where the result set of the SELECT is empty, the variable will just retain its current value (quite likely a non-NULL from the previous iteration) and you won't exit the loop.
This can obviously be avoided with intelligent coding, of course, but that difference between the two forms isn't all that well known, in my experience, so Bill's warning seems apt 🙂
Cheers!
EDIT: Tweaked some wording.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply