## Message Posted: Mon, 09 Feb 2004 @ 12:53:27 GMT

 < Last>>

 Subj: Re: Stored Procedure Performance and Transposing Data From: Vivek Pandey

Hi,

A similar problem was provided a solution by the below SQL.

```/*If the maximum number of rows is known (and small)*/
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
;
```
```/*If the maximum number of rows is unknown (or huge)
But it returns more than one line... */

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

You may modify it for your application.

Thanks, Vivek.

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback