this can give you an idea how it can work
WITH RECURSIVE base (c_year,c_month,c_date,day_of _month,c_list)
as
(
select year_of _calendar as c_year
,month_of _year as c_month
,calendar_date as c_date
,day_of _month
,Trim(cast(day_of _month!!':'!!calendar_date as \
varchar(10000))) as c_list
from sys_calendar.calendar
where day_of _month = 1
union all
select c.year_of_calendar as c_year
,c.month_of_year as c_month
,c.calendar_date as c_date
,c.day_of_month
,b.c_list !! ',' !! \
trim(cast(c.day_of_month!!':'!!c.calendar_date as varchar(10000))) as \
c_list
from sys_calendar.calendar c
join
base b
on b.c_year = c.year_of_calendar
and b.c_month = c.month_of_year
and b.day_of_month + 1 = c.day_of_month
)
select c_year,c_month,c_list
from base
where add_months(c_date - extract(day from c_date)+1,1)-1 = c_date \
and c_year = 2011Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
sorry - you need to remove the \...
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
If the number of elements you want to concatenate is low, you can do something like this:
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) ||
/*** There're are more rows than expeted ***/
max(case when rnk > 8 then ',...' else '' end) as ConcatenatedRows
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
;
This is an example from Dieter Noeth: http://www.tek-tips.com/viewthread.cfm?qid=747901
Suprisingly, I'm not finding many examples of this out there.
I have a table like:
id foo
-- ----
1 yes
2 yes
3 no
i want a 1 row result set like:
bar
----
1: yes, 2: yes, 3: no
I've been attempting to use recursive queries to no avail. Does anyone have any experience trying this?