Need to put output in one line

[DFW1999]

I have a table ORD which has one BTN(0000000000). This BTN has 9 entries in the DTL table( 9 products associated to this BTN). I need to format the out put in this fixed length ONE LINE output:

BTN prod_count(9)nm1 nm2 nm3 nm4 nm5 nm6 nm7 nm8 nm9

NOTE: I put spaces between prods for readability purpose.


Here is the query whcih gives me all 9 pordcuts but in nine lines not on the same line.

select BTN
, prod_nm
from
( select ORD.BTN, PROD.PROD_NM
from ORD left outer join DTL
on ORD.ORD_ID=DTL.ORD_ID
inner join prod
on prod.prod_id=DTL.prod_id
where ORD.BTN='0000000000'
group by ORD.BTN, prod.PROD_NM) X
group by 1,2
order by 1,2

BTN PROD_NM
0000000000 NM1
0000000000 NM2
0000000000 NM3
0000000000 NM4
0000000000 NM5
0000000000 NM6
0000000000 NM7
0000000000 NM8
0000000000 NM9

Any suggestions?
Thanks,
DFW1999

 

[farhy]

Hi,

you have to join nine tables to get this result set.

Here is the SQL Stmt to get 3 rows.

Select
T0.*
, T1.NM1
, T2.NM2
, T3.NM3
FROM ORD T0
left outer join DTL T1
on T0.ORD_ID=T1.ORD_ID
left outer join DTL T2
on T0.ORD_ID=T1.ORD_ID
left outer join DTL T3
on T0.ORD_ID=T1.ORD_ID
where T0.BTN='0000000000'

sincerly
farhy

 

[dnoeth]

This is an example from my trainings.
It's using dbc.columns as example, so you can simply run it to see how it works.


/*** Rows to columns ***/
/*** If the maximum number of rows is known (and small).
Returns one column per row ***/
sel
databasename
,tablename
,max(case when rnk = 1 then ColumnName else '' end) as Col1
,max(case when rnk = 2 then ColumnName else '' end) as Col2
,max(case when rnk = 3 then ColumnName else '' end) as Col3
,max(case when rnk = 4 then ColumnName else '' end) as Col4
,max(case when rnk = 5 then ColumnName else '' end) as Col5
,max(case when rnk = 6 then ColumnName else '' end) as Col6
,max(case when rnk = 7 then ColumnName else '' end) as Col7
,max(case when rnk = 8 then ColumnName else '' end) as Col8
from
(
sel
databasename
,tablename
,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 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 columns ***/
/*** If the maximum number of rows is unknown (or huge).
Returns maybe several rows ***/
sel
databasename
,tablename
,trim(((rnk / 8) * 8) + 1 (format '999')) || ' to ' ||
trim(((rnk / 8) + 1) * 8 (format '999')) as Columns
,max(case when rnk mod 8 = 0 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 1 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 2 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 3 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 4 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 5 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 6 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 7 then ColumnName else '' end) (title '')
from
(
sel
databasename
,tablename
,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 ***/
/*** If the maximum number of rows is unknown (or huge).
Returns maybe several rows of concatenated strings ***/
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