Print
All Forums Tools
gnanareddy 19 posts Joined 03/14
02 Feb 2015
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.

dnoeth 4628 posts Joined 11/04
02 Feb 2015

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

 

Dieter

gnanareddy 19 posts Joined 03/14
03 Feb 2015

Thank you very much Dieter. Working as expected.

You must sign in to leave a comment.