Concatanation of the rows from one columns

Database
Enthusiast

Concatanation of the rows from one columns

Hey, I want to concatenate all the rows of one column. For exampe

ColName

Val1

Val2

Val3

I want my result to be Val1, Val2, Val3

Thx,Anna

21 REPLIES
Supporter

Re: Concatanation of the rows from one columns

As a string? And how do you order the values?

Check recusive queries.

Enthusiast

Re: Concatanation of the rows from one columns

Yes as a string, I have a table that holds all the values i need to concatanate (no need to order) - i thought maybe doing it with cursor ?

Do you have an idea how to do this ?

thanks, anna

Supporter

Re: Concatanation of the rows from one columns

Try to avoid coursor as they do not scale in TD.

Recurisve queries can do the trick - if you can come up with an order and an first element.

Check 

http://forums.teradata.com/forum/database/string-concat-returning-multiple-rows-in-one-string-cocat-...

(in the sql the \ need to be removed)

Enthusiast

Re: Concatanation of the rows from one columns

Thanks for the recursive calendar example.

i need eventually to build a dynamic query that has this values concatanated inside.

Could you please post some code here ?

Thanks, Anna

Supporter

Re: Concatanation of the rows from one columns

I don't understand your specific requirement.

You would need to explain this in more detail.

Enthusiast

Re: Concatanation of the rows from one columns

Sure :)

I have a table with one column - TestName and I need to build a query that makes a column from each testname (I try to build a pivot with a non fix number of columns)

TestName 

T1

T2

T3

So I need to concatenate the names of this tests in one row in order to filter the testnames in the where clause:

WHERE TestName IN (T1, T2, T3, ...)

And also concatenate this test names to build the CASE statements:

SELECT

Column1,

SUM (case when TestName= 'T1' THEN Test_Result else NULL END)

AS T1,

SUM (case when TestName= 'T2' THEN Test_Result else NULL END)

AS T2,

SUM (case when TestName= 'T3' THEN Test_Result else NULL END)

AS T3

FROM

(

....

WHERE TestName IN (T1, T2, T3, ...)

)

GROUP BY

Column1

 

 

I hope its more clear now.

I guess that I do need to use a cursor in order to build this query dynamically.

Thanks alot for your help!

Anna

Supporter

Re: Concatanation of the rows from one columns

This might give you an idea

SELECT SQL_TEXT
FROM
(
select CAST(-1 AS INTEGER) AS ID,
CAST('Select year_of_calendar !! ''-'' !! month_of_year,' AS VARCHAR(200)) AS SQL_TEXT
from sys_calendar.calendar
where calendar_date = date
union all
select row_number() over (order by day_of_month) as id,
Case when id = 1 then ' ' else ' ,' end !!'SUM (case when day_of_month = '!! trim (day_of_month) !!' THEN day_of_week else NULL END) AS T_'!! trim(day_of_month)
from (select day_of_month from sys_calendar.calendar where year_of_calendar = extract(year from current_date) and month_of_year in (2,3) group by 1) as t
union all
select 1000 + row_number() over (order by calendar_date) as id,
case when id = 1001 then 'from sys_calendar.calendar'
when id = 1002 then 'where year_of_calendar = extract(year from current_date) and month_of_year in (2,3)'
when id = 1003 then 'and day_of_month in ('
end
from sys_calendar.calendar
where calendar_date between date and date + 2
union all
select 2000 + row_number() over (order by day_of_month) as id,
case when id = 2001 then ' ' !!trim(day_of_month)
else ' ,' !! trim(day_of_month)
end
from (select day_of_month from sys_calendar.calendar where year_of_calendar = extract(year from current_date) and month_of_year in (2,3) group by 1) as t
union all
select 3000 + row_number() over (order by calendar_date) as id,
case when id = 3001 then ')'
when id = 3002 then 'group by 1;'
end
from sys_calendar.calendar
where calendar_date between date and date + 1
) AS T
ORDER BY ID

;

it doesn't require recursive queries - which are still an option...

Enthusiast

Re: Concatanation of the rows from one columns

Ulrich this can be great! And this drives us back to the begining of this post - Now we would want to concatanate all the value of the column SQL_TEXT to one single value that hold all the SQL command that can be run against TERADATA to get resultset.

Is it possible to combine all of the column values ?

Anna

Supporter

Re: Concatanation of the rows from one columns

sure (use a recursive query over the id) but lets assume you have this single string - how do you execute it? How do you fetch the result - rememper number of columns can differ...