May 24, 2011 at 3:07 pm
Hello,
Here is a sample data I am using.
CREATE TABLE #temp
(ae1 varchar,
ae2 varchar,
ae3 varchar,
ae4 varchar)
INSERT INTO #temp SELECT 1,2,null,null
INSERT INTO #temp SELECT null,NULL,3,4
INSERT INTO #temp SELECT null,null,NULL,4
INSERT INTO #temp SELECT NULL,NULL,3,null
INSERT INTO #temp SELECT 1,null,null,null
I need to return this as
ae1ae2ae3ae4Error Codes
12NULLNULL1, 2
NULLNULL343, 4
NULLNULLNULL44
NULLNULL3NULL3
1NULLNULLNULL1
I can't seem to figure out how to concatenate all the fields properly, especially when there are null values
I tried Stuff() but wasn't having much luck although I figure my syntax was wrong.
Any hints are appreciated!
May 24, 2011 at 4:02 pm
There may be a more elegant solution that looks and/or performs better but this query gives the results you asked for:
CREATE TABLE #temp
(
ae1 VARCHAR,
ae2 VARCHAR,
ae3 VARCHAR,
ae4 VARCHAR
)
INSERT INTO #temp SELECT 1,2,null,null
INSERT INTO #temp SELECT null,NULL,3,4
INSERT INTO #temp SELECT null,null,NULL,4
INSERT INTO #temp SELECT NULL,NULL,3,null
INSERT INTO #temp SELECT 1,null,null,null
WITH cte(ae1, ae2, ae3, ae4, concat_string)
AS (
SELECT ae1,
ae2,
ae3,
ae4,
ISNULL(ae1 + ',', '') + ISNULL(ae2 + ',', '') + ISNULL(ae3 + ',', '') + ISNULL(ae4 + ',', '') AS concat_string
FROM #temp
)
SELECT ae1,
ae2,
ae3,
ae4,
LEFT(concat_string, LEN(concat_string) - 1) AS concat_string
FROM cte ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 7:36 pm
whoops... opc.three has it covered.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 24, 2011 at 9:50 pm
One tweak I would make to mine is to trade in the two separate function calls to LEFT and LEN for one call to STUFF:
CREATE TABLE #temp
(
ae1 VARCHAR,
ae2 VARCHAR,
ae3 VARCHAR,
ae4 VARCHAR
) ;
INSERT INTO #temp SELECT 1,2,NULL,NULL ;
INSERT INTO #temp SELECT NULL,NULL,3,4 ;
INSERT INTO #temp SELECT NULL,NULL,NULL,4 ;
INSERT INTO #temp SELECT NULL,NULL,3,NULL ;
INSERT INTO #temp SELECT 1,NULL,NULL,NULL ;
INSERT INTO #temp SELECT NULL,NULL,NULL,NULL ;
WITH cte ( ae1, ae2, ae3, ae4, concat_string )
AS (
SELECT ae1,
ae2,
ae3,
ae4,
ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, '') AS concat_string
FROM #temp
)
SELECT ae1,
ae2,
ae3,
ae4,
STUFF(concat_string, 1, 1, '') AS concat_string
FROM cte ;
Edit
PS and at that point who needs the cte?
SELECT ae1,
ae2,
ae3,
ae4,
STUFF(ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, ''), 1, 1, '') AS concat_string
FROM #temp ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 25, 2011 at 7:28 am
Thanks so much, I knew I was close but the last post really cleared up how the STUFF function works!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy