CODEWITHSUNDEEP
sqlsql-server-2012
full-stack
full-stack

Reputation: 553

sql concatenate columns if one or more columns is not null

I have a table that looks something like this:

BuildingID | RouterType | RouterPort | RouterInstaller | Notes
-----------+------------+------------+-----------------+-------
282        | Linksys    | 1990       | Super           | NULL
307        | Sonic Wall | NULL       | Greg            | NULL
311        | NULL       | NULL       | NULL            | NULL

I would like the Notes column to be the concatenation of the 2nd 3rd and 4th columns only if the column is not null.

line 1: Router Type: Linksys Router Port: 1990 Router Installer: Super
line 2: Router Type: Sonic Wall Router Installer: Greg
line 3: NULL

Also the word 'Router Type:' should only come in if the value of Router type is not null etc.

I am pretty new to SQL - any help would be greatly appreciated.

Upvotes: 0

Views: 4624

Answers (5)

Michał Turczyn
Michał Turczyn

Reputation: 37347

Try this:

select case when [Note] = '' then null else Note from (
    select BulidingId,
            case when RouterType is null then '' else 'Router Type: ' + RouterType + '; '+
            case when RouterPort is null then '' else 'Router Port: ' + RouterPort + '; '+
            case when RouterInstaller is null then '' else 'Router Port: ' + RouterInstaller + '; '+
            case when Notes is null then '' else 'Notes: ' + Notes + '; ' [Note]
    from MY_TABLE
) a

Upvotes: 2

baroale
baroale

Reputation: 56

Try this update statement:

DECLARE @TEMP_TABLE TABLE (
    BUILDING_ID INT,
    ROUTER_TYPE VARCHAR(20) NULL,
    PORT INT NULL,
    ROUTER_INSTALLER VARCHAR(20) NULL,
    NOTES VARCHAR(1000) NULL
)

INSERT INTO @TEMP_TABLE VALUES(1,'Linksys Router', 1990, 'Super', NULL)
INSERT INTO @TEMP_TABLE VALUES(2,NULL, NULL, NULL, NULL)

UPDATE @TEMP_TABLE 
SET NOTES = COALESCE(' Router type: ' + ROUTER_TYPE,'') + COALESCE(' Port: ' + CAST(PORT AS VARCHAR),'') + COALESCE(' Router installer: ' + ROUTER_INSTALLER,'')
WHERE ROUTER_TYPE IS NOT NULL OR PORT IS NOT NULL OR ROUTER_INSTALLER IS NOT NULL

SELECT * FROM @TEMP_TABLE

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

To get the spacing correct, I recommend:

select stuff(coalesce(' RouterType: ' + RouterType), '') +
             coalesce(' RouterPort: ' + RouterPort ), '') +
             coalesce(' RouterInstaller: ', RouterInstaller), ''),
             1, 1, ''
            ) as Notes
from t;

In an update:

update t
    set notes = stuff(coalesce(' RouterType: ' + RouterType), '') +
                      coalesce(' RouterPort: ' + RouterPort ), '') +
                      coalesce(' RouterInstaller: ', RouterInstaller), ''),
                      1, 1, ''
                     );

Note: This will not put in a NULL value, instead using an empty string. That is easily fixed -- if it is a problem:

update t
    set notes = nullif(stuff(coalesce(' RouterType: ' + RouterType), '') +
                             coalesce(' RouterPort: ' + RouterPort ), '') +
                             coalesce(' RouterInstaller: ', RouterInstaller), ''),
                             1, 1, ''
                            ), ''
                      )

Upvotes: 0

Ankush Sankhyan
Ankush Sankhyan

Reputation: 66

Try this select query, i think it will help you:

SELECT CASE WHEN (
  COL2 IS NOT NULL 
   AND COL3 IS NOT NULL 
   AND  COL4 IS NOT NULL )
    THEN 
        CONCAT(COL2,' ', COL3,' ', COL4) END as ConcatedData,
     * from YOUR_TABLE;

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12684

This will do it by combining Coalesce and Concat. The column names are added as labels to the column values.

select COALESCE(Notes, COALESCE(CONCAT(COALESCE(CONCAT('RouterType: ',RouterType),''), 
                       COALESCE(CONCAT(' RouterPort: ',RouterPort ),''),
                       COALESCE(CONCAT(' RouterInstaller: ',RouterInstaller),'')), NULL)) as Notes
from yourTable;

Upvotes: 2

Related Questions