Comma separated Value
-
Hi,
I need the query to store the list of values in single column using comma separated.
For Example,
Empid
1
2
3
Should get result as
1,2,3
Thanks in Advance,
Anitha
-
Use COALESCE
DECLARE @aa VARCHAR(1000)
SELECT @aa = COALESCE(@aa,'') + EmpId+ ' ,'
FROM YourTable
SELECT @aa AS EmpIds
-
Use ISNULL. I think people use COALESCE way too much when it's not appropriate, and ISNULL is certainly faster.
Edit -> in fact, wtf am I talking about, use neither. Just setting it to an empty string first avoids calling ISNULL / COALESCE on each row, and is faster still. I need more coffee!
DECLARE @aa VARCHAR(1000)
SET @aa = ''
SELECT @aa = @aa + EmpId+ ', '
FROM YourTable
SELECT @aa AS EmpIds
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone -
Matt Whitfield (10/5/2009)
Use ISNULL. I think people use COALESCE way too much when it's not appropriate, and ISNULL is certainly faster.Edit -> in fact, wtf am I talking about, use neither. Just setting it to an empty string first avoids calling ISNULL / COALESCE on each row, and is faster still. I need more coffee!
DECLARE @aa VARCHAR(1000)
SET @aa = ''
SELECT @aa = @aa + EmpId+ ', '
FROM YourTable
SELECT @aa AS EmpIds
You have to be careful with this method though as any Nulls in column will give a null result
-
You have to be careful with NULL with either method. The COALESCE version will just give you the stream of data since the last null, rather than skipping over them...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone -
I'm glad that someone else realizes how comparatively slow COALESCE is compared to ISNULL or a preset empty string.
However, my real question to the OP is (drum roll, please)... why do you need to do this? This is a form of denormalization that shouldn't normally be done in a database. As always, I ask this question because, depending on what you actually want to do, there may be a better way.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes thanks for that information,
after doing a test on 10million rows , ISNULL seems a lot quicker. I had been used to using COALESCE since it is the ANSI standard but now I know..
I guess it shows that it is always best to test things out..
-
I still want to know why the OP wants to do this type of denormalization, though...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello? Is this thing on? 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Matt Whitfield (10/5/2009)
Use ISNULL. I think people use COALESCE way too much when it's not appropriate, and ISNULL is certainly faster.Interesting comment about ISNULL being faster. The results here, especially for SS2008, suggest otherwise.
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
The article concludes:
'I ran these tests several times on a few different servers, and ISNULL appears to pretty consistently out-perform COALESCE by an average of 10 or 12 percent.'
?
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone -
Matt Whitfield (10/7/2009)
The article concludes:'I ran these tests several times on a few different servers, and ISNULL appears to pretty consistently out-perform COALESCE by an average of 10 or 12 percent.'
?
Look at the comments at the end
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Ahh I see - interesting he didn't update the article 😀
I see results on my boxes as being faster for 2000 & 2005 and equitable on 2008, using that method.
Interestingly, though, I see ISNULL being a lot faster in actual use - for example...
WITH Test AS (SELECT TOP 1000000 CONVERT([bigint], [ap].[object_id]) AS object_id FROM [sys].[all_parameters] [ap] CROSS JOIN [sys].[all_parameters] [ap1])
SELECT SUM(COALESCE([Test].[object_id], 0)) FROM [Test]
is consistently slower than
WITH Test AS (SELECT TOP 1000000 CONVERT([bigint], [ap].[object_id]) AS object_id FROM [sys].[all_parameters] [ap] CROSS JOIN [sys].[all_parameters] [ap1])
SELECT SUM(ISNULL([Test].[object_id], 0)) FROM [Test]
on both my 2005 and 2008 rigs...
One of those arguments for 'test, test and test again'
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone -
Matt Whitfield (10/7/2009)
Ahh I see - interesting he didn't update the article 😀I see results on my boxes as being faster for 2000 & 2005 and equitable on 2008, using that method.
Interestingly, though, I see ISNULL being a lot faster in actual use - for example...
WITH Test AS (SELECT TOP 1000000 CONVERT([bigint], [ap].[object_id]) AS object_id FROM [sys].[all_parameters] [ap] CROSS JOIN [sys].[all_parameters] [ap1])
SELECT SUM(COALESCE([Test].[object_id], 0)) FROM [Test]
is consistently slower than
WITH Test AS (SELECT TOP 1000000 CONVERT([bigint], [ap].[object_id]) AS object_id FROM [sys].[all_parameters] [ap] CROSS JOIN [sys].[all_parameters] [ap1])
SELECT SUM(ISNULL([Test].[object_id], 0)) FROM [Test]
on both my 2005 and 2008 rigs...
One of those arguments for 'test, test and test again'
Yep, I seem to get the same results for 2005/2008 - your query runs quicker with ISNULL, but the queries from the article run quicker with COALESCE on 2008 and pretty much the same with 2005.
Hmm, explanations anyone?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply