Concatenate one column values into single row value

Tools
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.