December 29, 2011 at 6:55 am
hi,
i Have a table which contains a column called "value".
The records are as below:
Value
-------
1
2
3
4
5
6
7
i need the output as single records as:
1,2,3,4,5,6,7
The logic is delimiting all the values by comma,
In oracle , we implement this using wm_concat().
Is there anyway to his in achieve this in sqlserver.
Thanks
Thanks,
Pandeeswaran
December 29, 2011 at 7:05 am
pandeesh (12/29/2011)
i Have a table which contains a column called "value".The records are as below:
Value
-------
1
2
3
4
5
6
7
i need the output as single records as:
1,2,3,4,5,6,7The logic is delimiting all the values by comma,
In oracle , we implement this using wm_concat().
Is there anyway to his in achieve this in sqlserver.
Yes. Search this forum for "row to column", zillions of posts 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2011 at 7:13 am
Adding another thought. Is there any reason for doing it in database side? It should be done on Front End (application).
December 29, 2011 at 7:15 am
Dev (12/29/2011)
Adding another thought. Is there any reason for doing it in database side? It should be done on Front End (application).
Why? What's wrong with backend code formatting data as needed?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2011 at 7:20 am
PaulB-TheOneAndOnly (12/29/2011)
Dev (12/29/2011)
Adding another thought. Is there any reason for doing it in database side? It should be done on Front End (application).Why? What's wrong with backend code formatting data as needed?
It looks like presentation / display requirement and if Front End options are available then it should be done there only.
I prefer to keep my database server away from unnecessary tasks.
December 29, 2011 at 7:27 am
Dev (12/29/2011)It looks like ...
...but you don't know, huh? why don't just answer what op asked for instead of going border line off topic for no good reason?
Note: even if it has a question mark it is not really a question.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2011 at 7:31 am
No. This is not a business requirement. and just i want to achieve this in sql server.
i am looking in PIVOT to do this.
Thanks
Thanks,
Pandeeswaran
December 29, 2011 at 7:42 am
i believe, it's not possible in PIVOT since i have only one field here.
Thanks,
Pandeeswaran
December 29, 2011 at 7:45 am
PaulB-TheOneAndOnly (12/29/2011)
Dev (12/29/2011)It looks like ...
...but you don't know, huh? why don't just answer what op asked for instead of going border line off topic for no good reason?
Note: even if it has a question mark it is not really a question.
I didn’t expect such a reply. We are not here just to answer whatever been asked. Most of the times, OP come here with bad requirements or wrong assumptions in mind. For Example ‘Please help me in my Cursor’. It’s our responsibility to warn them.
I am trying to understand your disagreement but not getting a single reason for it.
December 29, 2011 at 8:28 am
the below code works fine:
SELECT STUFF( -- Remove first comma
(
SELECT ', ' + [ID] FROM -- create comma separated values
(
SELECT cast([ID] as VARCHAR) as [ID] FROM test --Your query here
) AS T FOR XML PATH('')
)
,1,1,'')
Where,
"ID" is the column name in the table "test".
Any other suggestions welcome...
Thanks,
Pandeeswaran
December 29, 2011 at 10:42 am
pandeesh (12/29/2011)
Any other suggestions welcome...
How about using coalesce?
create table #test(id decimal);
insert into #test values(1);
insert into #test values(2);
insert into #test values(3);
insert into #test values(4);
insert into #test values(5);
insert into #test values(6);
insert into #test values(7);
declare @MyStuff varchar(256)
select @MyStuff = coalesce(@MyStuff + ',', '') + cast(id as varchar)
from #test;
select @MyStuff
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 30, 2011 at 5:21 am
PaulB-TheOneAndOnly (12/29/2011)
pandeesh (12/29/2011)
Any other suggestions welcome...How about using coalesce?
create table #test(id decimal);
insert into #test values(1);
insert into #test values(2);
insert into #test values(3);
insert into #test values(4);
insert into #test values(5);
insert into #test values(6);
insert into #test values(7);
declare @MyStuff varchar(256)
select @MyStuff = coalesce(@MyStuff + ',', '') + cast(id as varchar)
from #test;
select @MyStuff
I would prefer the FOR XML trick. Concatenating that way is not reliable.
-- Gianluca Sartori
December 30, 2011 at 7:17 am
Gianluca Sartori (12/30/2011)
PaulB-TheOneAndOnly (12/29/2011)
pandeesh (12/29/2011)
Any other suggestions welcome...How about using coalesce?
create table #test(id decimal);
insert into #test values(1);
insert into #test values(2);
insert into #test values(3);
insert into #test values(4);
insert into #test values(5);
insert into #test values(6);
insert into #test values(7);
declare @MyStuff varchar(256)
select @MyStuff = coalesce(@MyStuff + ',', '') + cast(id as varchar)
from #test;
select @MyStuff
I would prefer the FOR XML trick. Concatenating that way is not reliable.
oh ... I like FOR XML better myself but poster wanted an alternative.
By the way, why is coalesce not reliable? don't tell me it gets tired and stops working after a while 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 2, 2012 at 2:46 am
There's nothing wrong with COALESCE, it's the assignment in the SELECT list that makes the code unreliable.
Here's a couple of articles on the subject:
http://www.sqlmag.com/article/tsql3/row-concatenation-solutions-aren-t-all-equal
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
-- Gianluca Sartori
January 2, 2012 at 5:37 pm
Gianluca Sartori (1/2/2012)
There's nothing wrong with COALESCE, it's the assignment in the SELECT list that makes the code unreliable.Here's a couple of articles on the subject:
http://www.sqlmag.com/article/tsql3/row-concatenation-solutions-aren-t-all-equal
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
Whup! Be careful there, ol' friend. 🙂 I have to take exception to that "unreliable" part. "It Depends" on how you use it and how you sort it. Just because a query doesn't work doesn't mean it's unreliable... it just very reliably doesn't work the way you wrote it. 😀 In fact, the first article you cited clearly states, and I quote "He found that although [font="Arial Black"]these solutions produce the same result[/font], the time it takes to get those results varies significantly."
I'll also prove that the second part (the part about time) of that is just because the author didn't optimize the "self-referencing variable".
One of the myths about self-referencing variables used in concatenation is that they're slow. They are only slow if you don't know how they actually work and the things that can slow them down. To find that out, please see the following article (a bit out of date but the lessons still apply)...
http://www.sqlservercentral.com/articles/Test+Data/61572/
Now... would you believe that the method of using a self-referencing variable in a scalar UDF can actually beat the XML method? Probably not so I'll prove it. 😀
Create the following. We have to use real tables instead of Temp Tables because we're going to create a Scalar Function that calls on the table...
--===== Identify a nice, safe place to do this that everyone has because
-- we need to conditionally drop a real table to test this.
USE TempDB
;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb.dbo.TestData','U') IS NOT NULL
DROP TABLE dbo.TestData
;
--===== Create and populate test table.
-- Column "RowNum" contains unique row numbers
-- Column "SomeID" contains non-unique numbers
-- (should be 1/400th of the row count to allow about 400 concatenated items per SomeInt).
-- Column "SomeCode" has a range of "AA" to "ZZ" non-unique 2 character strings
SELECT TOP 1000000 --<<<LOOK! CHANGE THIS NUMBER TO CHANGE THE NUMBER OF ROWS!
RowNum = IDENTITY(INT,1,1),
SomeID = ABS(CHECKSUM(NEWID()))%2500+1, --<<<LOOK! CHANGE THIS NUMBER TO 1/400th THE ROW COUNT
SomeCode = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
INTO dbo.TestData
FROM sys.all_columns t1,
sys.all_columns t2
;
--===== Add what most folks would use as a PK/Clustered Index
ALTER TABLE dbo.TestData
ADD PRIMARY KEY CLUSTERED (RowNum)
;
--===== Add an index to help XML and the function we're going to create
CREATE INDEX IXC_TestData_Cover1 ON dbo.TestData(SomeID,SomeCode,RowNum)
;
--===== Conditionally drop the function to make reruns in SSMS easier
IF OBJECT_ID('tempdb.dbo.ConcatTest','FN') IS NOT NULL
DROP FUNCTION dbo.ConcatTest
;
GO
--===== Create the function
CREATE FUNCTION dbo.ConcatTest (@SomeID INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return+',','')+SomeCode
FROM dbo.TestData
WHERE SomeID = @SomeID
ORDER BY SomeCode
RETURN @Return
END
;
GO
I want you to notice that, despite what was said in the first article about a "small number of rows", the test data above contains a million rows with 2500 unique IDs and 400 "codes" to concatenate for each ID.
Here's the test code...
--===== Let the testing begin! First, conditionally drop the "target" tables
IF OBJECT_ID('tempdb..#VariableMethod','U') IS NOT NULL
DROP TABLE #VariableMethod
;
IF OBJECT_ID('tempdb..#XmlMethod','U') IS NOT NULL
DROP TABLE #XmlMethod
;
PRINT '--===== Run the test on the function method ============================';
SET STATISTICS TIME ON;
SELECT SomeID, dbo.ConcatTest(SomeID) AS CSVString
INTO #VariableMethod
FROM dbo.TestData
GROUP BY SomeID
SET STATISTICS TIME OFF;
PRINT '--===== Run the test on the XML method ============================';
SET STATISTICS TIME ON;
SELECT t1.SomeID,
STUFF((SELECT ','+t2.SomeCode
FROM dbo.TestData t2
WHERE t1.SomeID = t2.SomeID FOR XML PATH('')),1,1,'') AS CsvString
INTO #XmlMethod
FROM dbo.TestData t1
GROUP BY t1.SomeID;
SET STATISTICS TIME OFF;
Here's what I get for a return...
--===== Run the test on the function method ============================
SQL Server Execution Times:
CPU time = 2937 ms, elapsed time = 3709 ms.
(2500 row(s) affected)
--===== Run the test on the XML method ============================
SQL Server Execution Times:
CPU time = 4219 ms, elapsed time = 4363 ms.
(2500 row(s) affected)
Of course, we have to prove that the two methods did the same thing...
SELECT *
FROM #VariableMethod vm
FULL OUTER JOIN #XmlMethod xm
ON vm.SomeID = xm.SomeID
WHERE vm.CsvString <> xm.CsvString
Anyway... to summarize this post, variable overlays (self referencing variables as some call them) for purposes of concatenation are not unreliable... they actually guaranteed to not work if you do an ORDER BY with a forumula in it and you can rely on that ;-). And, for VARCHAR(8000), they're certainly not slow if you do it right.
Oh yeah... almost forgot. Since we created some real objects in Temp DB, here's the cleanup code to get rid fo them...
--===== Housekeeping to clean up the permanent objects we created in TempDB.
IF OBJECT_ID('tempdb.dbo.TestData','U') IS NOT NULL
DROP TABLE dbo.TestData
;
IF OBJECT_ID('tempdb.DROP FUNCTION dbo.ConcatTest','FN') IS NOT NULL
DROP FUNCTION dbo.ConcatTest
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply