SQL Server 2017 has a new String function which supports concatenating multiple values with separator, ex. Comma (,) or Pipe (|)
This function will come handy, if you want to generate a CSV file quickly
Syntax for the function is as follows
CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )
Example Usage
CREATE TABLE [dbo].[Test](
[intPK] [int] IDENTITY(1,1) NOT NULL,
[strData] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[intPK] ASC
)
)
GOINSERT INTO dbo.test (strData) VALUES (‘Test1’)
INSERT INTO dbo.test (strData) VALUES (‘Test2’)
INSERT INTO dbo.test (strData) VALUES (‘Test3’)
INSERT INTO dbo.test (strData) VALUES (‘Test4’)
INSERT INTO dbo.test (strData) VALUES (‘Test5’)SELECT CONCAT_WS(‘|’, intPK, strData) AS Result FROM dbo.Test
In above example we are concatenating 2 fields using pipe as separator
Output
NULL Handling
If any of the data fields has NULL, then we need to use ISNULL() or COALESCE() to handle this, else, the structure of the output will change
ALTER TABLE dbo.Test ADD strNullData VARCHAR(50) NULL
UPDATE dbo.Test SET strNullData=’A’ WHERE intPK=1
UPDATE dbo.Test SET strNullData=’B’ WHERE intPK=3
UPDATE dbo.Test SET strNullData=’C’ WHERE intPK=5SELECT CONCAT_WS(‘|’, intPK, strNullData, strData) Result FROM dbo.Test
In the below output, rows 1,3 and 5 has 3 fields and rows 2 and 4 has only 2 fields
Query with ISNULL() Handling
SELECT CONCAT_WS(‘|’, intPK, ISNULL(strNullData, ”), strData) Result FROM dbo.Test
Now the below output will have consistent list of fields
Hope you find this helpful !!!
Share this:
SQLXpertise 