All Forums Database
AnnaGam 11 posts Joined 07/12
04 Jul 2012
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

ulrich 816 posts Joined 09/09
04 Jul 2012

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

Check recusive queries.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AnnaGam 11 posts Joined 07/12
04 Jul 2012

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

ulrich 816 posts Joined 09/09
04 Jul 2012

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-row

(in the sql the \ need to be removed)

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AnnaGam 11 posts Joined 07/12
04 Jul 2012

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

ulrich 816 posts Joined 09/09
04 Jul 2012

I don't understand your specific requirement.
You would need to explain this in more detail.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AnnaGam 11 posts Joined 07/12
04 Jul 2012

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

ulrich 816 posts Joined 09/09
05 Jul 2012

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...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AnnaGam 11 posts Joined 07/12
05 Jul 2012

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

ulrich 816 posts Joined 09/09
05 Jul 2012

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...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AnnaGam 11 posts Joined 07/12
05 Jul 2012

Maybe I can do a CREATE TABLE target_table AS
(Our SELECT  .... )

I'm not sure im following you. So what is the best way to do this ? a BTEQ ?

Thx,Anna
 

ulrich 816 posts Joined 09/09
05 Jul 2012

Check Jimms comment in 

http://forums.teradata.com/forum/tools/bteq-export-issue

You could paste the SQL below into the BTEQ script - no need for a single row SQL - and try it.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AnnaGam 11 posts Joined 07/12
05 Jul 2012

Ok, thx I want to try this. Im using TD Version 13.0.0.11 from aPRIL 2010 - How can I reach bteq ?

Thx, Anna

ulrich 816 posts Joined 09/09
05 Jul 2012

ask your it people or colleagues.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AnnaGam 11 posts Joined 07/12
05 Jul 2012

Thx I will :)

Anna

yaragalaramesh 20 posts Joined 06/12
16 Jul 2012

Hi, i need your help,

 

  I have data in the file like as follows

ID   NAMe                                       col3         col4          col5

1    penchala,Ramesh,yaganti,      bnbn       hjhh            ghhjhj

2    gaddam,nagarjuna,gaga         bnbnnm   hjhjhjh       kjfddjk

.

.

.

.

.

 

  Like this i have data my problem is i need to use the the delimeter  as   ',' but in one column the field value itself has the delimeter if use delemeter ',' that column two values is taking as different columns values how to do this can please help me?

 

Thanks in advance

 

Ram

Ram

Qaisar Kiani 337 posts Joined 11/05
17 Jul 2012

While concatenating, if you are restricted to use the comma delimiter then you can probably do a string operation on the table and change the comma delimiter to pipe or some other one in the NAME column. 

stevemogilevski 3 posts Joined 05/12
31 Jul 2012

This is also depends how the exported data used. For example if you loading to Xcel you may add "  " around your string with comas....

extreme_logic 3 posts Joined 12/11
19 Feb 2013

Here's a link with a sample.
http://thesimpleprogrammer.blogspot.com/2013/02/concat-rows-as-single-column-in-teradata.html

archanair 1 post Joined 10/13
10 Oct 2013

hi i want a help ,
I am working on some data now . I got to do certain operations on a set of column .It is actually phone numbers.but has been created in integer and decimal datatypes . actually i want it to be in string datatype inorder to use substring or 'like' function .
My requirement is to get  the the first three digits of the phone number.As it is in decimal and integer type how i can i perform the requiremnt.

dnoeth 4628 posts Joined 11/04
10 Oct 2013
substring(trim(col) from 1 for 3))

Dieter

Dieter

omng392 8 posts Joined 06/12
28 Jan 2014

Here is a good query that I use to recursively find and create a sting of values for compression.  In it you will see how I concatenate the values together.
 
DROP TABLE VT_TEMP;
 
CREATE MULTISET VOLATILE TABLE VT_TEMP
AS
( SELECT putyourcolumnnamehere AS COL
, COUNT(*) AS ROW_CNT
, ROW_NUMBER() OVER ( ORDER BY COUNT(*) DESC ) AS ROW_RANK
FROM databasenamehere.tablenamehere
WHERE COL IS NOT NULL
GROUP BY 1
QUALIFY (ROW_NUMBER() OVER ( ORDER BY COUNT(*) DESC ) ) <= 200
)
WITH DATA PRIMARY INDEX( COL )
ON COMMIT PRESERVE ROWS;
 
 
--INSERT THE RESULTS INTO THAT TABLE
 
WITH RECURSIVE CNT_LIST( R_RANK, TXT )
AS
(
SELECT ROW_RANK
, TRIM(CAST(COL AS VARCHAR(6000)))
 
FROM VT_TEMP
WHERE ROW_RANK = 1
 
UNION ALL
 
SELECT V.ROW_RANK
, TXT || ',' || TRIM(CAST(V.COL AS VARCHAR(50)))
FROM VT_TEMP V INNER JOIN CNT_LIST C
ON V.ROW_RANK = R_RANK + 1
)
 
SELECT TXT
FROM CNT_LIST
QUALIFY (ROW_NUMBER() OVER( ORDER BY TXT DESC)) = 1;

You must sign in to leave a comment.