concatenate character column 2

[scarletAni]

I have a table with values
1 the
1 value
1 is
1 one
2 the
2 value
2 two

I need the output as

1 The value is one
2 the value two


as in the character column containig some description needs to be concatenated. Its if it were an integer, then we can do a SUM and group by. Since its a character, how to achieve what I want ?

 

[RogerCruse]

Hi scarletAni,
I haven't quite got the answer your looking for because I can't find a way to interpret the original sequence of your data, here's my solution...

Using the following as test data explains the problem with original sequence better -
create multiset table tt_table (
col1 char(1),
col2 char(6))
primary index (col1);

insert into tt_table ('A','The');
insert into tt_table ('A','cat');
insert into tt_table ('A','sat');
insert into tt_table ('A','on ');
insert into tt_table ('A','the');
insert into tt_table ('A','mat');
insert into tt_table ('B','Mary');
insert into tt_table ('B','had');
insert into tt_table ('B','a ');
insert into tt_table ('B','little');
insert into tt_table ('B','lamb');

Then the following SQL -
SELECT tt1.col1 || ' ' ||
trim(case when tt1.col2 is null then ' ' else tt1.col2 end) || ' ' ||
trim(case when tt2.col2 is null then ' ' else tt2.col2 end) || ' ' ||
trim(case when tt3.col2 is null then ' ' else tt3.col2 end) || ' ' ||
trim(case when tt4.col2 is null then ' ' else tt4.col2 end) || ' ' ||
trim(case when tt5.col2 is null then ' ' else tt5.col2 end) || ' ' ||
trim(case when tt6.col2 is null then ' ' else tt6.col2 end) colzzz
FROM (select col1, col2
from (SELECT col1, col2, rank(col2, col1) col3 FROM tt_table GROUP BY 1) ttx1
where col3=1) tt1
LEFT JOIN
(select col1, col2
from (SELECT col1, col2, rank(col2, col1) col3 FROM tt_table GROUP BY 1) ttx2
where col3=2) tt2
ON tt1.col1=tt2.col1
LEFT JOIN
(select col1, col2
from (SELECT col1, col2, rank(col2, col1) col3 FROM tt_table GROUP BY 1) ttx3
where col3=3) tt3
ON tt1.col1=tt3.col1
LEFT JOIN
(select col1, col2
from (SELECT col1, col2, rank(col2, col1) col3 FROM tt_table GROUP BY 1) ttx4
where col3=4) tt4
ON tt1.col1=tt4.col1
LEFT JOIN
(select col1, col2
from (SELECT col1, col2, rank(col2, col1) col3 FROM tt_table GROUP BY 1) ttx5
where col3=5) tt5
ON tt1.col1=tt5.col1
LEFT JOIN
(select col1, col2
from (SELECT col1, col2, rank(col2, col1) col3 FROM tt_table GROUP BY 1) ttx6
where col3=6) tt6
ON tt1.col1=tt6.col1
LEFT JOIN
(select col1, col2
from (SELECT col1, col2, rank(col2, col1) col3 FROM tt_table GROUP BY 1) ttx7
where col3=7) tt7
ON tt1.col1=tt7.col1;

Gives -
colzzz
B Mary little lamb had a
A the sat on mat cat
A The sat on mat cat


I hope that helps!

Roger...

 

[scarletAni]

Roger,
How have you arrived at 6 joins ? Is it because There are 6 'A' entries ?
In that case if we are not sure of hte number of entries for each id 'A', 'B' etc, then how do we arrive at the number of joins ?

 

[RogerCruse]

scarletAni,
I probably got carried away with my cutting & pasting!

Roger...

 

[rrrkrishnan]

scarletAni,
If you are not sure of the number of entries for each id 'A', 'B' etc, you can only do this by writing a stored procedure.

 

[SiMCard]

Stored procedure or generated code (exported and then invoked) would do it. It's the classic 'can you cross-tab in Teradata?' question. The straight answer is no - but there are work arounds.

Rog - you lifted that from my Stats Collection script generator!

Si M...

 

[dnoeth]

This is an example i use in my trainings.

If the number of rows to be concatenated is known/small:
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
;

If the number of rows to be concatenated is unknown/huge, it's impossible to retrieve only a single row using plain SQL. But even SPs/cursors will fail if you exceed VARCHAR(64000) ...

sel
databasename
,tablename
,trim(((rnk / 8) * 8) + 1 (format '999')) || ' to ' ||
trim(((rnk / 8) + 1) * 8 (format '999')) as RowNumber
,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 ConcatenatedRows
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
;


Dieter

 

[RogerCruse]

Hi Dieter,
Excellent. A very elegant solution!

Roger...