×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

concatenate character column
2

concatenate character column

concatenate character column

(OP)
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 ?

RE: concatenate character column

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

RE: concatenate character column

(OP)
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 ?

RE: concatenate character column

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

Roger...

RE: concatenate character column

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.

RE: concatenate character column

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


RE: concatenate character column

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

RE: concatenate character column

Hi Dieter,
Excellent. A very elegant solution!

Roger...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close