String Concat - returning multiple rows in one string cocat row

Database

String Concat - returning multiple rows in one string cocat row

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? 

5 REPLIES
Supporter

Re: String Concat - returning multiple rows in one string cocat row

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 = 2011

Supporter

Re: String Concat - returning multiple rows in one string cocat row

sorry - you need to remove the \...

Re: String Concat - returning multiple rows in one string cocat row

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

Re: String Concat - returning multiple rows in one string cocat row

Hi Everyone,

Can someone tell me what is the data type consideration here for 456.

sel 123||'abc'||456 as CONCATINATE;

sel 123||'abc'||456 as CONCATINATE;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

CONCATINATE
-------------
123abc 456

Here 123 is considered as BYTEINT type . so it is taking 4 chars '----' and right justified.

abc as char.

Not able to under stand for 456 ===> its taking format as '------' and 456 as right justified.

It seems to be it was considering this as SMALLINT but not able to understand ... why?

Thank You..!

Senior Apprentice

Re: String Concat - returning multiple rows in one string cocat row

Please don't add new questions to old posts, post new topic by pressing the "CREATE A NEW FORUM TOPIC" button on top of each forum's main page.

The range of a BYTEINT is between -128 and 127, and 456 is clearly outside of that range.

The format of a BYTEINT is '-(3)9', but '-(5)9' for a SMALLINT:

123 -> ' 123'

456 -> '   456' 

Of course you mixed different data types in your query, which results in automatic typecasts using old Teradata style (right aligned digits).

Explicit CASTs on the other hand return left aligned digits.

Another way is sel TRIM(123)||'abc'||TRIM(456).