 |
 |
Archives of the TeradataForum
Message Posted: Mon, 10 Jul 2006 @ 17:34:15 GMT
| Subj: | | Re: Converting Rows to coulmns by some Fucnction of teradata |
| |
| From: | | Dieter Noeth |
I got an example from my trainings concatenating all column names of a table. Not simple, but quite fast (probably faster than a recursive
query in V2R6). Depending on the data RANK has to be replaced by ROW_NUMBER:
/*** Rows to concatenated string ***/
/*** If the maximum number of rows is known (and small).
Returns a single concatenated string consisting of up to 8
rows***/
sel
databasename
,tablename
,max(case when rnk = 1 then ColumnName else '' end) ||
max(case when rnk = 2 then ',' || ColumnName else '' end) ||
max(case when rnk = 3 then ',' || ColumnName else '' end) ||
max(case when rnk = 4 then ',' || ColumnName else '' end) ||
max(case when rnk = 5 then ',' || ColumnName else '' end) ||
max(case when rnk = 6 then ',' || ColumnName else '' end) ||
max(case when rnk = 7 then ',' || ColumnName else '' end) ||
max(case when rnk = 8 then ',' || ColumnName else '' end) ||
/*** Indicating more than 8 rows ***/
max(case when rnk > 8 then ',...' else '' end) as Columns
from
(
sel
databasename
,tablename
,trim(columnName) as ColumnName
,rank() over (partition by databasename, tablename
order by columnid) as rnk
from
dbc.columns
where databasename = 'dbc'
) dt
group by 1,2
order by 1,2
;
/*** Rows to concatenated string ***/
/*** If the maximum number of rows is unknown (or huge).
Returns maybe several rows of concatenated strings per group***/
sel
databasename
,tablename
,trim(((rnk / 8) * 8) + 1 (format '999')) || ' to ' ||
trim(((rnk / 8) + 1) * 8 (format '999')) as ColumnNumber
,max(case when rnk mod 8 = 0 then ColumnName else '' end) ||
max(case when rnk mod 8 = 1 then ',' || ColumnName else '' end) ||
max(case when rnk mod 8 = 2 then ',' || ColumnName else '' end) ||
max(case when rnk mod 8 = 3 then ',' || ColumnName else '' end) ||
max(case when rnk mod 8 = 4 then ',' || ColumnName else '' end) ||
max(case when rnk mod 8 = 5 then ',' || ColumnName else '' end) ||
max(case when rnk mod 8 = 6 then ',' || ColumnName else '' end) ||
max(case when rnk mod 8 = 7 then ',' || ColumnName else '' end) as
Columns
from
(
sel
databasename
,tablename
,trim(columnName) as ColumnName
,rank() over (partition by databasename, tablename
order by columnid) -1 as rnk
from
dbc.columns
where databasename = 'dbc'
) dt
group by 1,2,3
order by 1,2,3
;
/*** Rows to concatenated string ***/
/*** Nested version instead of hundreds of CASEs.
Returns a single concatenated string consisting of up to 2048
columnnames ***/
sel
databasename
,tablename
,max(case when rnk mod 16 = 0 then ColumnName else '' end) ||
max(case when rnk mod 16 = 1 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 2 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 3 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 4 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 5 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 6 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 7 then ',' || ColumnName else '' end)
as Columns
from
(
sel
databasename
,tablename
,rnk / 16 as rnk
,max(case when rnk mod 16 = 0 then ColumnName else '' end) ||
max(case when rnk mod 16 = 1 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 2 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 3 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 4 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 5 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 6 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 7 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 8 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 9 then ',' || ColumnName else '' end) ||
max(case when rnk mod 16 = 10 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 11 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 12 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 13 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 14 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 15 then ',' || ColumnName else '' end)
as ColumnName
from
(
sel
databasename
,tablename
,rnk / 16 as rnk
,max(case when rnk mod 16 = 0 then ColumnName else '' end) ||
max(case when rnk mod 16 = 1 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 2 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 3 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 4 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 5 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 6 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 7 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 8 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 9 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 10 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 11 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 12 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 13 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 14 then ',' || ColumnName else '' end)
||
max(case when rnk mod 16 = 15 then ',' || ColumnName else '' end)
as ColumnName
from
(
sel
databasename
,tablename
,trim(columnName) as ColumnName
,rank() over (partition by databasename, tablename
order by columnid) -1 as rnk
from
dbc.columns
where databasename = 'dbc'
) dt
group by 1,2,3
)dt
group by 1,2,3
) dt
group by 1,2
;
Dieter
| |
