help in Transpose rows to columns

Database
KVB
Enthusiast

help in Transpose rows to columns

Hi All,

 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

index1

index2

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

Regards

KVB

7 REPLIES
Enthusiast

Re: help in Transpose rows to columns

Hi,

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 :).

Cheers,

Junior Contributor

Re: help in Transpose rows to columns

@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))
FROM dbc.IndicesV
WHERE databasename = 'dbc'
AND TableName = 'UDTCast'
GROUP BY 1,2
KVB
Enthusiast

Re: help in Transpose rows to columns

Thank you Dieter.

Enthusiast

Re: help in Transpose rows to columns

Hi Dieter,

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.

I/p

1 a

1 b

2 c

2 d

desired o/p 

1 a,b

2 c,d

--Samir

Junior Contributor

Re: help in Transpose rows to columns

Hi Samir,

by default only dbc has access to tdstats, but you might also use XMLAGG when XML-services are installed, see this post: 

http://forums.teradata.com/forum/database/convert-a-column-into-a-comma-separated-list-0#comment-141...

Enthusiast

Re: help in Transpose rows to columns

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 ;)

--Samir

Re: help in Transpose rows to columns

Dieter, is there some caution that should be used when granting execute on the tdstats.concat function?
What impact could there be?
Can you point me in the correct direction? (documentation)