September 26, 2023 at 10:57 am
Hi All,
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'
September 26, 2023 at 11:04 am
SELECT STRING_AGG(CONCAT('''',td.name,''''),',')
FROM #tbl_db td
September 26, 2023 at 2:10 pm
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
September 26, 2023 at 2:44 pm
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.
September 26, 2023 at 4:05 pm
Thanks both and it is working good.
Thanks Drew for the script and insert statement.
September 26, 2023 at 8:37 pm
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
September 26, 2023 at 9:08 pm
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
September 27, 2023 at 5:53 pm
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
November 4, 2023 at 10:14 pm
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy