I have a requirement.
SELECT COLUMNNAME FROM DBC.INDICES WHERE DATABASENAME='DBNAME' AND TABLENAME='TNAME' ORDER BY COLUMNPOSITION
The above statement returns the indexes applied on tha table.It can be in any number.
For eg:I have combination of 2 columns in index.it returns 2 rows.let's say
Here I need to transpose the rows to a single column in the below format.
I need my output in this fashion (index1,index2)
If I have combination of 3 rows.The query returns 3 rows and my output should be (index1,index2,index3).
I am in TD 14 and not in TD 14.10. I have heard about TD_UNPIVOT. It transposes rows to columns. I am not sure whether it transforms columns to rows :).
@Raja: UNPIVOT transforms columns to rows, unfortunately there's no PIVOT function, yet.
@KVB: Check my StatsInfo view, ColumnName returns a comma-delimited list.
Btw, in TD14 there's a concat-UDF in TDStats:
SELECT TableName, IndexNumber, tdstats.udfconcat(TRIM(ColumnName))
WHERE databasename = 'dbc'
AND TableName = 'UDTCast'
GROUP BY 1,2
I am not able to execute tdstats.udfconcat as i dont have access to it. Even dba id doesn;t have access to execute this. I am wondering why this access is not even to dba id ? Is this something not given deliberately for some safe reasons ? is tdstats a systems DB to which only the super user should have access ? What's the way out. I have a situation in which i need to transform from row to columns.
by default only dbc has access to tdstats, but you might also use XMLAGG when XML-services are installed, see this post:
Thanks Dietet, XMLAGG works perfect. One question though, why have we not exposed tdstats to all users. This is a very helpful function. I tested it somehow ;)