Search code examples
csvt-sqlconcatenation

Concatenate table values with semicolon and double quotes

I need to provide a text file that concatenate all the results from a table, but the delimiter needs to be semicolons and the values needs to be wrapped with double quotes. I need to return the column headers as well. How can I do this by just using TSQL.

Example below (Note what happens with empty values and NULL's)

DROP TABLE IF EXISTS #Temp

CREATE TABLE #Temp
(
    Name           VARCHAR(8) NULL,
    Age            VARCHAR(8) NULL,
    City           VARCHAR(8) NULL
)

INSERT INTO #Temp (Name, Age, City)
VALUES ('Mike','', NULL ) -- Age is blank and City is NULL

SELECT CONCAT_WS('";"'
      ,'Name'
      ,'Age'
      ,'City')
UNION all
SELECT CONCAT_WS('";"'
      ,Name
      ,Age
      ,City) 
FROM #Temp

-- Results needs to be....
/*
    "Name";"Age";"City"
    "Mike";"";""
*/
Solution

  • To match your expected output results:

    SELECT '"' + CONCAT_WS('";"'
  ,'Name'
  ,'Age'
  ,'City') +'"'
UNION all
SELECT '"' +CONCAT_WS('";"'
  ,Name
  ,ISNULL(Age,'')
  ,ISNULL(City,'')) +'"'
FROM #Temp