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"
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.
Suppose, create table txt, and have column text_id and value is:
You Need
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 );
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;