Concatenate one column values into single row value

Tools & Utilities
Enthusiast

Concatenate one column values into single row value

Hi,

Can you help me how to fetch multicolumn index columns table vice.

From dbc.indices we will get  o/p as below:

==============================

DBName TBName IndexType  Columnname

ABC        Table1        P                 ID1

ABC        Table1        P                 ID2

But need the O/P as below:

===================

DBName TBName IndexType  Columnname

ABC        Table1        P                 ID1, ID2

=============================================

Kindly help me the SQL Query.

2 REPLIES
Junior Contributor

Re: Concatenate one column values into single row value

If your TD system includes XML, this is the simplest way:

SELECT
DatabaseName
,TABLENAME
,IndexNumber
,IndexType
,TRIM(TRAILING ',' FROM (XMLAGG(TRIM(Columnname)|| ','
ORDER BY ColumnPosition) (VARCHAR(1000))))
FROM dbc.IndicesV
GROUP BY 1,2,3,4
Enthusiast

Re: Concatenate one column values into single row value

Thank you very much Dieter. Working as expected.