0

I have a table "CONSTANTS" with column "TEXT_ID" and it has some data in it, for example 5 rows

TEXT_ID

  • A
  • B
  • C
  • D
  • E

I need an SQL select query which returns "A,B,C,D,E"

1
  • What you tried so far? Commented Jun 12, 2014 at 5:19

4 Answers 4

1

You can use the below Query

select stuff((select ','+convert(varchar(100),Text_Id) from your_table_name for xml path('')),1,1,'');
Sign up to request clarification or add additional context in comments.

Comments

0

If you are using MySQL as database then GROUP_CONCAT function would do the job. If you are using Oracle then, here are some techniques which can do the job depending upon Oracle version.

Comments

0

If You Used Oracle 11g you can Apply this Query::

Suppose, create table txt, and have column text_id and value is:

text_id:

  • A
  • B
  • C
  • D
  • E

You Need

text_id

A,B,C,D,E

So You Can Apply Below Query..

WITH t AS 
  (SELECT 'A' AS col FROM txt
  union
  SELECT 'B' AS col FROM txt
  union
  SELECT 'C' AS col FROM txt
  union
SELECT 'D' AS col FROM txt
union
  SELECT 'E' AS col FROM txt)
SELECT SUBSTR(MAX(col),2) TEXT_ID
FROM (SELECT SYS_CONNECT_BY_PATH(col, ',') col
      FROM ( SELECT col,
                    ROW_NUMBER() OVER (ORDER BY col) VAR
              FROM t)
      START WITH VAR= 1
      CONNECT BY PRIOR VAR= VAR- 1 );

Comments

0

FOR SQL SERVER:

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID;

FOR ORACLE:

WITH TABLE_NAME AS
(SELECT 'A' TEXT_ID FROM DUAL UNION SELECT 'B' TEXT_ID FROM DUAL UNION SELECT 'C' TEXT_ID FROM DUAL)

SELECT SUBSTR (SYS_CONNECT_BY_PATH (TEXT_ID , ','), 2) csv
      FROM (SELECT TEXT_ID , ROW_NUMBER () OVER (ORDER BY TEXT_ID ) rn,
                   COUNT (*) OVER () cnt
              FROM TABLE_NAME)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.