Sql Server: Best Way to Concatenate Multiple Columns

SQL Server: Best way to concatenate multiple columns?

Through discourse it's clear that the problem lies in using VS2010 to write the query, as it uses the canonical CONCAT() function which is limited to 2 parameters. There's probably a way to change that, but I'm not aware of it.

An alternative:

SELECT '1'+'2'+'3'

This approach requires non-string values to be cast/converted to strings, as well as NULL handling via ISNULL() or COALESCE():

SELECT  ISNULL(CAST(Col1 AS VARCHAR(50)),'')
      + COALESCE(CONVERT(VARCHAR(50),Col2),'')

How to concat multiple columns in sql

SELECT CONCAT(EMP_HOUSE_NO,' ', EMP_STREET_NAME,' ', EMP_AREA) AS
address

from EMPLOYEE_DETAILS

WHERE EMP_ID=1;

SQL Concat multiple columns + | for Join

Generally, the "" are used for object names. Your query should be like below (replace the "|" by '|'):

SELECT dbo.[U1$Job Task].[Job No_] AS Job, 
       dbo.[U1$Job Task].[Job Task No_] AS [Cost Code], 
       dbo.[U1$Job Task].Description, 
       dbo.[SFI Barcode Entry].MapID
FROM   dbo.[U1$Job Task] LEFT OUTER JOIN dbo.[SFI Barcode Entry] 
ON concat( concat(concat (dbo.[U1$Job Task].[Job No_], '|'), concat (dbo.[U1$Job Task].[Job Task No_], '|')), dbo.[U1$Job Task].[Job Task Type])= dbo.[SFI Barcode Entry].MapValue 
LEFT OUTER JOIN dbo.U1$Job 
ON dbo.[U1$Job Task].[Job No_] = dbo.U1$Job.No_
WHERE (dbo.U1$Job.Status = 2)

how to concatenate multiple column values into a single column?

More than one way to achieve this:

SELECT CONCAT(first_name, ' ' ,last_name) AS full_name;

For earlier versions (Where CONCAT is not a built in function):

SELECT first_name + ISNULL(' ' + last_name, '') as Full_Name from [YourTable]

This as well should give you the same result

SELECT COALESCE(first_name, '') + COALESCE(last_name, '') as FullName FROM [YourTable]

Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic

Here is a dynamic way

Slightly different approach from your static query. To make it dynamic, Use while loop or CURSOR to generate the Task1 + Task2 + ..TaskN. Then use it in Select query.

DECLARE @columns VARCHAR(50)='Task1,Task2,Task3', -- Pass the list of column names 
        @int     INT = 1,
        @sql     VARCHAR(8000)

SET @sql = '    ;WITH cte
         AS (SELECT *,
                    ''Cycle-'' + Cast(CycleNum AS VARCHAR(10)) + ''::'' '

WHILE @int <= Len(@columns) - Len(Replace(@columns, ',', '')) -- To find the number of Tasks in list
  BEGIN
      SET @sql += + '+''Task' + Cast(@int AS VARCHAR(10))
                  + '~''+' + 'Cast(Task'
                  + Cast(@int AS VARCHAR(10))
                  + ' AS VARCHAR(50)) + '','''
      SET @int += 1
  END

SET @sql += '   AS concat_dates
             FROM   #tempTable)
    SELECT DISTINCT FacilityName,
                    LEFT(CycleData, Len(CycleData) - 1)
    FROM   cte a
           CROSS apply(SELECT b.concat_dates + '',''
                       FROM   cte b
                       WHERE  a.FacilityName = b.FacilityName
                       FOR xml path('''')) cs (CycleData) 

                       '
--print @sql -- uncomment it to debug if you have any error when executing dynamic code
EXEC (@sql) 

Not to worry about the usage of While Loop/CURSOR since we are not doing any resource intensive operations inside the loop.

Static Query will looking like this

;WITH cte
     AS (SELECT *,
                'Cycle-' + Cast(CycleNum AS VARCHAR(10))
                + '::' + 'Task1~' + Cast(Task1 AS VARCHAR(50))
                + ',' + 'Task2~' + Cast(Task2 AS VARCHAR(50))
                + ',' AS concat_dates
         FROM   #tempTable)
SELECT DISTINCT FacilityName,
                LEFT(CycleData, Len(CycleData) - 1)
FROM   cte a
       CROSS apply(SELECT b.concat_dates + ','
                   FROM   cte b
                   WHERE  a.FacilityName = b.FacilityName
                   FOR xml path('')) cs (CycleData) 

Concatenate SQL columns with comma separated

You can concat separators conditionally. This will output an empty string if either of the columns are null or empty.

select concat(col1,
              case when len(col2)>1 then ',' else '' end,
              col2,
              case when len(col3)>1 then ',' else '' end,
              col3) 
from your_table;

To output null if either of the columns are null or empty, wrap the concat inside a nullif like this

select nullif(concat(col1,
              case when len(col2)>1 then ',' else '' end,
              col2,
              case when len(col3)>1 then ',' else '' end,
              col3),'')
from your_table;

How to combine multiple columns into one column?

SELECT Column1 FROM TableName
UNION ALL
SELECT Column2 FROM TableName
UNION ALL
SELECT Column3 FROM TableName

If you don't want duplicate values, use UNION instead of UNION ALL.

You can also do this using UNPIVOT operator

SELECT Column123
FROM
(
  SELECT Column1, Column2, Column3 
  FROM TableName
) AS tmp
UNPIVOT 
(
  Column123 FOR ColumnAll IN (Column1, Column2, Column3)
) AS unpvt;

https://www.w3schools.com/sql/sql_union.asp

https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/

---

Related Topics