July 9, 2019 at 3:05 pm
I have 6 nullable fields, which I need to insert into a single, comma separated value. The query I'm working with however needs to emphasize performance, so I was just curious what's the best way to achieve the following, taking performance into account?
CREATE TABLE #MyTable (Id INT, Val1 VARCHAR(10), Val2 VARCHAR(10), Val3 VARCHAR(10), Val4 VARCHAR(10), Val5 VARCHAR(10), Val6 VARCHAR(10))
INSERT INTO #MyTable SELECT 1, 'abc', 'def', 'ghi', NULL, 'mno', NULL
INSERT INTO #MyTable SELECT 2, 'this', NULL, 'is', 'just', 'a', 'test'
--the below select statement doesn't work correctly
SELECT Id, (ISNULL(Val1, '') + ', ' + ISNULL(Val2, '') + ', ' + ISNULL(Val3, '') + ', ' + ISNULL(Val4, '') + ', ' + ISNULL(Val5, '') + ', ' + ISNULL(Val6, '')) as FinalValue
FROM #MyTable
DROP TABLE #MyTable
The problem with the code above, is commas are automatically included, but I need to exclude the comma if that particular Val is null.
So currently, my result is as follows:
ID Final Value
--------------------------------------
1 abc, def, ghi, , mno,
2 this, , is, just, a, test
But I actually need the following:
ID Final Value
--------------------------------------
1 abc, def, ghi, mno
2 this, is, just, a, test
Thanks
July 9, 2019 at 3:38 pm
Use COALESCE()
SELECT
Id
, FinalValue = (ISNULL(Val1, '') + ', ' + ISNULL(Val2, '') + ', '
+ ISNULL(Val3, '') + ', ' + ISNULL(Val4, '') + ', '
+ ISNULL(Val5, '') + ', ' + ISNULL(Val6, '')
)
, FinalValue = (COALESCE(Val1 + ', ', '') + COALESCE(Val2 + ', ', '') + COALESCE(Val3 + ', ', '')
+ COALESCE(Val4 + ', ', '') + COALESCE(Val5 + ', ', '') + COALESCE(Val6 + ', ', '')
)
FROM #MyTable;
July 9, 2019 at 3:39 pm
Came up with this but I am curious to know if there is a more elegant way to achieve it
SELECT Id ,
CASE WHEN RIGHT(( ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
+ +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',', '')
+ ISNULL(Val5 + ',', '') + ISNULL(Val6, '') ), 1) = ','
THEN LEFT (( ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
+ +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',',
'')
+ ISNULL(Val5 + ',', '') + ISNULL(Val6, '') ),
LEN(ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
+ +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',',
'')
+ ISNULL(Val5 + ',', '') + ISNULL(Val6, ''))
- 1)
ELSE ( ISNULL(Val1 + ',', '') + ISNULL(Val2 + ',', '')
+ +ISNULL(Val3 + ',', '') + ISNULL(Val4 + ',', '')
+ ISNULL(Val5 + ',', '') + ISNULL(Val6, '') )
END FinalValue
FROM #MyTable;
July 9, 2019 at 3:42 pm
SELECT Id, CONCAT_ws (',',VAL1,VAL2,VAL3,VAL4,VAL5,VAL6)
FROM #MyTable
or
SELECT Id, (ISNULL(Val1+ ', ' , '') + ISNULL(Val2+ ', ', '') + ISNULL(Val3+ ', ' , '')+ ISNULL(Val4+ ', ' , '') + ISNULL(Val5+ ', ' , '') + ISNULL(Val6, '')) as FinalValue
FROM #MyTable
July 9, 2019 at 6:03 pm
Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma). It's simpler to handle this using leading commas rather than trailing commas. Here is a solution.
SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
FROM #MyTable AS mt
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 9, 2019 at 7:28 pm
Use CONCAT and plus:
Declare @val1 varchar(10) = 'abc'
, @val2 varchar(10) = 'def'
, @val3 varchar(10) = Null
, @val4 varchar(10) = 'jkl';
Select concat(@val1 + ',', @val2 + ',', @val3 + ',', @val4);
If you want to insure that blanks are treated as nulls you can wrap each value with NULLIF. You also have to check for the last value being NULL or blank and if so you need to remove the last comma:
Something like this:
Declare @val1 varchar(10) = 'abc'
, @val2 varchar(10) = 'def'
, @val3 varchar(10) = Null
, @val4 varchar(10) = '';
Select FinalValue = iif(right(v.val, 1) = ',', left(v.val, len(v.val) - 1), v.val)
From (
Values (concat(nullif(@val1, '') + ',', nullif(@val2, '') + ',', nullif(@val3, '') + ',', nullif(@val4, '')))
) v(val);
Or this:
Declare @val1 varchar(10) = 'abc'
, @val2 varchar(10) = 'def'
, @val3 varchar(10) = Null
, @val4 varchar(10) = '';
Select t.FinalValue
From (Values (concat(nullif(@val1, '') + ',', nullif(@val2, '') + ',', nullif(@val3, '') + ',', nullif(@val4, '')))) v(val)
Cross Apply (Values (iif(right(v.val, 1) = ',', left(v.val, len(v.val) - 1), v.val))) t(FinalValue);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 9, 2019 at 8:20 pm
Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma). It's simpler to handle this using leading commas rather than trailing commas. Here is a solution.
SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
FROM #MyTable AS mtDrew
As with all else, "It Depends". In most cases, I want that trailing comma if the last value is null. In fact, I'd want empty strings for embedded values that have a null.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2019 at 8:41 pm
drew.allen wrote:Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma). It's simpler to handle this using leading commas rather than trailing commas. Here is a solution.
SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
FROM #MyTable AS mtDrew
As with all else, "It Depends". In most cases, I want that trailing comma if the last value is null. In fact, I'd want empty strings for embedded values that have a null.
The OP's desired results specifically exclude trailing commas.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 9, 2019 at 9:59 pm
Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma). It's simpler to handle this using leading commas rather than trailing commas. Here is a solution.
SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
FROM #MyTable AS mtDrew
FYI - if any of the values are empty string you will have extra commas and could have an extra column at the end. To handle that - and remove just the leading comma:
Declare @val1 varchar(10) = 'abc'
, @val2 varchar(10) = 'def'
, @val3 varchar(10) = Null
, @val4 varchar(10) = '';
Select t.FinalValue
From (Values (concat(',' + nullif(@val1, ''), ',' + nullif(@val2, ''), ',' + nullif(@val3, ''), ',' + nullif(@val4, '')))) v(val)
Cross Apply (Values (stuff(v.val, 1, 1, ''))) t(FinalValue);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 10, 2019 at 2:19 am
Jeff Moden wrote:drew.allen wrote:Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma). It's simpler to handle this using leading commas rather than trailing commas. Here is a solution.
SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
FROM #MyTable AS mtDrew
As with all else, "It Depends". In most cases, I want that trailing comma if the last value is null. In fact, I'd want empty strings for embedded values that have a null.
The OP's desired results specifically exclude trailing commas. Drew
Crud. My apologies, Drew. I totally blew it. I didn't even look at the original post. You're absolutely correct.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2019 at 12:20 pm
Thanks to everyone for the help! I ultimately went with Drew's solution, as I need to ensure there was no trailing comma.
SELECT mt.Id, STUFF(COALESCE(', ' + Val1, '') + COALESCE(', ' + Val2, '') + COALESCE(', ' + Val3, '') + COALESCE(', ' + Val4, '') + COALESCE(', ' + Val5, '') + COALESCE(', ' + Val6, ''), 1, 2, '') AS csv
FROM #MyTable AS mt Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply