CODEWITHSUNDEEP
sqlsql-servert-sql
SHELL
SHELL

Reputation: 25

How to use the stuff function with multiple columns

I'm trying to concatenate two columns from two different tables using the 'Stuff' function. I want the disp_name and the meas_value in one column separated by a comma...

    SELECT GDATA.DISP_NAME
          , MEAS.MEAS_VALUE
    FROM PT_MEAS AS meas
    LEFT JOIN G_DATA AS Gdata ON meas.MEAS_ID = Gdata.MEAS_ID

Upvotes: 0

Views: 602

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82474

Seems to me like you're confused about what Stuff actually does.
I can't say that this confusion is uncommon, though the documentation of this function is very clear and straight forward.

What the stuff function does is a very simple: it stuffs one string inside another.
Lets look at a few of simple examples:

SELECT  STUFF('ABCD', 1, 0, '|') As A,
        STUFF('ABCD', 1, 1, '|') As B,
        STUFF('ABCD', 3, 0, '|') As C,
        STUFF('ABCD', 3, 1, '|') As D

Results: 

A       B       C       D
|ABCD   |BCD    AB|CD   AB|D

As you can see, the first parameter is the string to stuff into, the second parameter is the index to start stuffing, the third parameter is the tricky part. It's the length to remove from the original string. If it's 0, then no chars are removed from the original string. The fourth parameter is the string to stuff.

Now that you know this, it should be obvious to you why stuff is simply irrelevant to what you're attempting to do here.

What you actually want is to use concat and +, like this:

SELECT CONCAT(GDATA.DISP_NAME +',', MEAS.MEAS_VALUE) As [new column name]
FROM PT_MEAS AS meas
LEFT JOIN G_DATA AS Gdata ON meas.MEAS_ID = Gdata.MEAS_ID

Now you might be wondering why using both concat and the string concatenation operator, instead just one of them - so let me explain.
When you use the + operator, and one of the operands is null, the result will be null as well - so null + 'abc' will actually become null.
However, when you use concat, it simply ignores null values and treat them like an empty string, so concat(null, 'abc') will become abc.
Now since the first column is coming from the right table, it means it can be null, and in that case, you probably don't want the comma, but only the second column, That's why I've concatenated the comma to the first column using +.
However, you probably would want to get back the second value, since it's not null, that's why I've used concat to concatenate it to the result.

Upvotes: 0

fgiuliani
fgiuliani

Reputation: 390

SELECT GDATA.DISP_NAME + ',' + MEAS.MEAS_VALUE
FROM PT_MEAS MEAS
    LEFT JOIN G_DATA AS GDATA ON MEAS.MEAS_ID = GDATA.MEAS_ID

Why do you need to use the STUFF function?

Upvotes: 1

Alan Burstein
Alan Burstein

Reputation: 7918

You don't need STUFF for this but here's how you do it with STUFF and without...

-- Sample Data
DECLARE @t TABLE (col1 VARCHAR(10), col2 VARCHAR(10));
INSERT @t VALUES('abc','xxx'),('fff','ggg');

-- Solution
SELECT t.*, 
  oldSchool     = t.col1+','+t.col2,
  [using STUFF] = STUFF(t.col1+'x',LEN(t.col2)+1,LEN(t.col2),','+t.col2)
FROM   @t AS t;

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

You can try as given below

DECLARE @concatNames VARCHAR(8000)
SELECT @concatNames = STUFF((Select ','+ GDATA.DISP_NAME + ' ' + MEAS.MEAS_VALUE 
from PT_MEAS as meas 
LEFT JOIN G_DATA as Gdata 
on meas.MEAS_ID = Gdata.MEAS_ID
FOR XML PATH('')),1,1,'');

Upvotes: 0

Related Questions