Transform rows to one column string

Database
Enthusiast

Transform rows to one column string

Hi All!

Have Teradata 13.10. I have request which return some rows and I want to get them as a string with delimiters

Example:

Have:

1

2

3

Need:

1,2,3

I need this without dinamyc SQL, procs and macro - I need only clear SQL (in one request)

PS: in Oracle I do this with wm_concat, xmlagg, sys_connect_by_path  etc... but I can't find this in Teradata =(

Please, help if you can

6 REPLIES
Enthusiast

Re: Transform rows to one column string

Update:

needless to say - number of result rows is unknown before run request.

Supporter

Re: Transform rows to one column string

Enthusiast

Re: Transform rows to one column string

thanks, but this is true for some way related data.

Now I have follow rows (from field dbc.allrights.accessright for some user):

CV
DU
DV
...

And finally I want to get this string:

CV,DU,DV,...

I can't use row_number, aggregate functions, subqueries and some other elements for some way relate my rows becouse recursive queries don't support it

In the end I want to get all grants for all users separately:

GRANT CREATE TABLE, DROP TABLE, ... ON ... TO;

Now I get it in so long form:

GRANT CREATE TABLE,
DROP TABLE,
 ...
 ON ... TO;
Enthusiast

Re: Transform rows to one column string

You can use the below method to change columns to rows:

Select D1.Databasename,D1.username,

coalesce(Max(CASE WHEN PosN =0 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =1 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =2 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =3 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =4 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =5 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =6 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =7 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =8 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =9 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =10 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =11 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =12 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =13 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =14 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =15 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =16 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =17 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =18 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =19 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =20 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =21 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =22 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =23 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =24 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =25 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =26 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =27 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =28 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =29 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =30 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =31 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =32 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =33 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =34 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =35 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =36 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =37 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =38 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =39 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =40 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =41 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =42 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =43 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =44 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =45 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =46 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =47 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =48 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =49 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =50 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =51 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =52 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =53 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =54 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =55 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =56 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =57 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =58 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =59 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =60 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =61 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =62 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =63 THEN D1.Accessright END), '') ||' '||

coalesce(Max(CASE WHEN PosN =64 THEN D1.Accessright END), '')

AccessRight

From

( select username username,databasename Databasename,accessright,

Row_Number () Over (Partition By username,Databasename Order By accessright ) as PosN

from dbc.allrights b

where username=user

group by 1,2,3

) As D1

group by 1,2

--------------------------------

 

I think we have only 61 or 64 distinct access rights, to change the numbers use the below recursive function

WITH RECURSIVE tmp (

counter

) AS (

SELECT

s.counter

FROM

(SELECT 0 AS counter) AS s -- lower limit

UNION ALL

SELECT

t.counter + 1 AS counter

FROM

tmp AS t

WHERE

t.counter < 64 -- upper limit

)

SELECT

'coalesce(Max(CASE WHEN PosN ='||trim( t.counter)||' THEN D1.Accessright END), '''') ||'' ''||'

FROM

tmp AS t;

Fan

Re: Transform rows to one column string

I'm using this SQL for selecting all Index Columns into single string:

Select  d1.DatabaseName

        ,D1.TableName

        ,D1.IndexName       

        ,IndexNumber

        ,IndexType  

        ,UniqueFlag

        ,min(CreateTimeStamp) as CreateTimeStamp

        ,max(LastAlterTimeStamp) as LastAlterTimeStamp         

        ,coalesce(Max(CASE WHEN ColumnPosition =1 THEN D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =2 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =3 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =4 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =5 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =6 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =7 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =8 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =9 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =10 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =11 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =12 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =13 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =14 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =15 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =16 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =17 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =18 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =19 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =20 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =21 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =22 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =23 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =24 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =25 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =26 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =27 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =28 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =29 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =30 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =31 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =32 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =33 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =34 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =35 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =36 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =37 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =38 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =39 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =40 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =41 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =42 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =43 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =44 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =45 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =46 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =47 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =48 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =49 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =50 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =51 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =52 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =53 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =54 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =55 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =56 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =57 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =58 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =59 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =60 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =61 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =62 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =63 THEN ', '||D1.ColumnName END), '') ||

           coalesce(Max(CASE WHEN ColumnPosition =64 THEN ', '||D1.ColumnName END), '')  As IndexColumns

         ,count(*) As ColumnCount

From

( SELECT trim(i.DatabaseName) As DatabaseName,

         trim(i.TableName) As TableName,

         trim(i.IndexName) As IndexName,

         trim(i.ColumnName) As ColumnName,

         i.IndexNumber,

         i.IndexType,

         i.ColumnPosition, 

         i.UniqueFlag,

         i.CreateTimeStamp,

         i.LastAlterTimeStamp         

FROM DBC.Indices i

) D1

Group by 1,2,3,4,5,6

Re: Transform rows to one column string