Concatenate table values with semicolon and double quotes-CodePudding

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";"";""
*/

CodePudding user response：

To match your expected output results:

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

CodePudding user response：

ISNULL is your friend.

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