CODEWITHSUNDEEP
sql-server
user8112761
user8112761

Reputation:

Concat Column Values with a separator in SQL Server

I want to concatenate column values with a separator and assign it to variable.

If column value is null, there's no need to add separator.

For example: A|B|C|D

If B is null A|C|D.

I tried with CONCAT function, but if B is null, it results in A||C|D

DECLARE @OldValue VARCHAR(8000); 

SELECT @OldValue =  CONCAT([FloorCode],'|',
                           [FloorName],'|',
                           [BuildingID],'|',
                           [HCMLocationCode],'|',
                           [IsActive]) 
FROM tblFloor_Master 
WHERE  FloorID = @FloorID;

@FloorID is an input parameter of SP

Upvotes: 3

Views: 18380

Answers (5)

J.Warren
J.Warren

Reputation: 778

I've only just discovered it too, but the CONCAT_WS (With Separator) function is designed for this problem. It has been available since SQL Server 2017.

DECLARE @OldValue VARCHAR(8000); 

SELECT @OldValue =  CONCAT_WS('|', 
                           [FloorCode],
                           [FloorName],
                           [BuildingID],
                           [HCMLocationCode],
                           [IsActive]) 
FROM tblFloor_Master 
WHERE  FloorID = @FloorID;

Upvotes: 4

Robson Patrick
Robson Patrick

Reputation: 1

SELECT TRIM ('|' FROM regexp_replace( CONCAT('','|','COLB','|','','|','COLD','|','COLE') ,'[\|]+' ,'|' ,'g' ) )

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Should be simple as doing replace, replace || to | then replace ||| to | since the maximum number of | is 3.

DECLARE @OldValue VARCHAR(8000); 

SELECT @OldValue =  replace(replace(CONCAT([FloorCode],'|',
                           [FloorName],'|',
                           [BuildingID],'|',
                           [HCMLocationCode],'|',
                           [IsActive]),'||','|'),'|||','|')
FROM tblFloor_Master 
WHERE  FloorID = @FloorID;

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try the following query.

create table tempTable (id int identity(1, 1),col1 char(1), col2 char(1), col3 char(1), col4 char(1))
insert into tempTable values ('A', NULL, 'C', 'D')

select * into #NewTable from(
select id, col1 as Value from tempTable where col1 is not null
union
select id, col2 as Value from tempTable where col2 is not null
union
select id, col3 as Value from tempTable where col3 is not null
union
select id, col4 as Value from tempTable where col4 is not null
)a

SELECT  ID
       ,STUFF((SELECT '| ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM #NewTable 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,1,' ') List_Output
FROM #NewTable t
GROUP BY ID

The output is as shown below

ID  List_Output
---------------
1     A| C| D

If you do not want to put space between values then you can try this

SELECT  ID
       ,STUFF((SELECT '|' + CAST(Value AS VARCHAR(10)) [text()]
         FROM #NewTable 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,1,'') List_Output
FROM #NewTable t
GROUP BY ID

In this case the output will be

ID  List_Output
---------------
1   A|C|D

You can also try the below actual query using stored procedure

create table tblFloor_Master (FloorID int identity(1, 1),
                              FloorCode char(1), 
                              FloorName char(1),
                              BuildingID char(1),
                              HCMLocationCode char(1))

insert into tblFloor_Master values ('A', NULL, 'C', 'D')

GO
--To create a procedure
create proc uspGetConcateValue
@FloorId int
as
BEGIN
    select * into #tblFloor_Master from(
    select FloorId, FloorCode as Value from tblFloor_Master where FloorCode is not null
    union
    select FloorId, FloorName as Value from tblFloor_Master where FloorName is not null
    union
    select FloorId, BuildingID as Value from tblFloor_Master where BuildingID is not null
    union
    select FloorId, HCMLocationCode as Value from tblFloor_Master where HCMLocationCode is not null
    )a

    SELECT  FloorId
           ,STUFF((SELECT '|' + CAST(Value AS VARCHAR(10)) [text()]
             FROM #tblFloor_Master 
             WHERE FloorId = t.FloorId
             FOR XML PATH(''), TYPE)
            .value('.','NVARCHAR(MAX)'),1,1,'') List_Output
    FROM #tblFloor_Master t
    where FloorID = @FloorId
    GROUP BY FloorId
END

Live demo here - Live Demo

Upvotes: 0

Rasanjana N
Rasanjana N

Reputation: 1400

SELECT @OldValue = CONCAT('',
    CASE WHEN [FloorCode] IS NULL THEN '' ELSE CONCAT([FloorCode],'|') END,
    CASE WHEN [FloorName] IS NULL THEN '' ELSE CONCAT([FloorName],'|') END,
    CASE WHEN [BuildingID] IS NULL THEN '' ELSE CONCAT([BuildingID],'|') END,
    CASE WHEN [HCMLocationCode] IS NULL THEN '' ELSE CONCAT([HCMLocationCode],'|') END,
    [IsActive])
FROM tblFloor_Master 
WHERE  FloorID = @FloorID;

Upvotes: 5

Related Questions