Convert columns result into rows with comma and single quotes

Question

Post reply

Convert columns result into rows with comma and single quotes

Saran

Right there with Babe

Points: 760

September 26, 2023 at 10:57 am

#4295400

Hi All,

I want to convert columns result into rows with comma.

Ex: Result should be - 'SQL1','ABCD100','XYZ123'

#code:

create table #tbl_db (name varchar(100))
insert into #tbl_db values ('SQL1')
insert into #tbl_db values ('ABCD100')
insert into #tbl_db values ('XYZ123')

select * from #tbl_db

Result needed: 'SQL1','ABCD100','XYZ123'

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

SELECT STRING_AGG(CONCAT('''',td.name,''''),',')
FROM #tbl_db td

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 2

Phil Parkin wrote:

SELECT STRING_AGG(CONCAT('''',td.name,''''),',')
FROM #tbl_db td

Instead of using CONCAT() use QUOTENAME(), in case your text contains single quotes. It will automatically create escaped forms of single quotes in your data.

I don't understand why people continue to use multiple insert statements when a simple table value constructor (TVC) will do the same thing without all of the clutter.

create table #tbl_db (name varchar(100));
insert into #tbl_db
VALUES ('SQL1')
     , ('ABCD100')
     , ('XYZ123')
     , ('O''Reily');

select * from #tbl_db;

SELECT STRING_AGG(QUOTENAME(td.[name], ''''), ',')
FROM #tbl_db AS td;

Here is a comparison of the results.

/*  CONCAT() version  */
'SQL1','ABCD100','XYZ123','O'Reily'

/*  QUOTENAME() version  */
'SQL1','ABCD100','XYZ123','O''Reily'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

drew.allen wrote:

I don't understand why people continue to use multiple insert statements when a simple table value constructor (TVC) will do the same thing without all of the clutter.
Drew

I agree! INSERT ... SELECT ... UNION ALL is even more old-school, but still appears.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Saran Right there with Babe Points: 760 More actions · Answer 4

Thanks both and it is working good.

Thanks Drew for the script and insert statement.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

drew.allen wrote:

SELECT STRING_AGG(QUOTENAME(td.[name], ''''), ',')
FROM #tbl_db AS td;

I prefer using CHAR instead of multiple single-quotes:

SELECT STRING_AGG(QUOTENAME(td.[name], CHAR(39)), ',')
FROM #tbl_db AS td;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

drew.allen SSC Guru Points: 76985 More actions · Answer 6

Jeffrey Williams wrote:

drew.allen wrote:
SELECT STRING_AGG(QUOTENAME(td.[name], ''''), ',')
FROM #tbl_db AS td;
I prefer using CHAR instead of multiple single-quotes:
SELECT STRING_AGG(QUOTENAME(td.[name], CHAR(39)), ',')
FROM #tbl_db AS td;

I can never remember what the correct CHAR number is, which is why I use multiple single quotes. The only CHAR I use with any frequency is CHAR(10), and it's almost exclusively in constructing XML documents. CHAR(13) doesn't show up the way I want it to in XML. So I use CHAR(10) to insert a LF without a CR.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

I just remember the ones I use the most - and CHAR(39) is one of those. For all others: https://www.asciitable.com/

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

The issue with using QUOTENAME is that it returns the result as an NVARCHAR(258). The OP started out with a VARCHAR().

In this case, the resulting STRING_AGG() returns an NVARCHAR(4000).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)