By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,301 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,301 IT Pros & Developers. It's quick & easy.

How to concat several lines into one

P: n/a
May
Hi,

My question is: How to concat values from several lines (the same
column) to only one value. For example:

We have table SGMENT with one column: NAME (varchar2(50)). We would
like to have as a query result the concatenation of names. I mean, for
example:

SGMENT
------
VS000_001_t
VS001_002_t
VS002_003_r

And with a query we would like to obtein:
"VS000_001_tVS001_002_tVS002_003_r"

Is this possible with only SQL Sentence as SELECT ....???

We are using IBM DB2 V7 UDB for OS390 AND ZOS

Thank you in advance

Regards
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Hi,
following works fine on DB2 8.2 for LUW, try this on your platform

WITH tmp (name, rn) AS
(SELECT root.name,1 FROM Segment as root WHERE root.name =
'VS000_001_t'
UNION ALL
SELECT parent.name||child.name, child.rn
FROM tmp parent, (SELECT name, rownumber() over(order by name) AS rn
FROM Segment) child
WHERE parent.rn+1 = child.rn
) select * from tmp order by 2 desc fetch first 1 row only

Cheers
Adrian

Nov 12 '05 #2

P: n/a
IBM DB2 UDB for OS/390 AND z/OS V7 doesn't support "WITH
common-table-expression" and "OLAP functions".

If the maximum number of names to be concatenated is limited.
Following query will work on DB2 UDB for OS/390 AND z/OS V7.

SELECT name1 || name2 || name3 || name4 || name5
|| name6 || name7 || name8 || name9 || name10 AS names
FROM (SELECT MAX(CASE WHEN n = 1 THEN RTRIM(name) ELSE '' END) name1
, MAX(CASE WHEN n = 2 THEN RTRIM(name) ELSE '' END) name2
, MAX(CASE WHEN n = 3 THEN RTRIM(name) ELSE '' END) name3
, MAX(CASE WHEN n = 4 THEN RTRIM(name) ELSE '' END) name4
, MAX(CASE WHEN n = 5 THEN RTRIM(name) ELSE '' END) name5
, MAX(CASE WHEN n = 6 THEN RTRIM(name) ELSE '' END) name6
, MAX(CASE WHEN n = 7 THEN RTRIM(name) ELSE '' END) name7
, MAX(CASE WHEN n = 8 THEN RTRIM(name) ELSE '' END) name8
, MAX(CASE WHEN n = 9 THEN RTRIM(name) ELSE '' END) name9
, MAX(CASE WHEN n =10 THEN RTRIM(name) ELSE '' END) name10
FROM (SELECT a.name
, COUNT(b.name)
FROM SGMENT a
, SGMENT b
WHERE a.name >= b.name
GROUP BY
a.name
) q (name, n)
) r

Nov 12 '05 #3

P: n/a
WITH is not available in V7 zOS, only in V8.
Row_number() fuction as well is not there on zOS.

Adrian, I am not sure about this kind of recursive SQL doing what you
like.
I tried because I thought its interesting, but no way.
Did you try it on LUW ?

May, what you are asking is about how to present your resultset,
especially when it is only one column.

This usually is done outside SQL. Are u using a tool ? I not, then a
simple rexx script will do the job of changing it to one line.

Juliane

Nov 12 '05 #4

P: n/a
Hi,
WITH is not available in V7 zOS, only in V8.
Row_number() fuction as well is not there on zOS. It's a pitty. This really nice features.
Did you try it on LUW ?

Yes. I tried it on LUW.

CREATE TABLE Segment ( name varchar(50) )
DB20000I The SQL command completed successfully.

insert into Segment
values('VS000_001_t'),('VS001_002_t'),('VS002_003_ r')
DB20000I The SQL command completed successfully.

WITH tmp (name, rn) AS (SELECT root.name,1 FROM Segment as root WHERE
root.name = 'VS000_001_t' UNION ALL SELECT parent.name||child.name,
child.rn FROM tmp parent, (SELECT name, rownumber() over(order by name)
AS rn FROM Segment) child WHERE parent.rn+1 = child.rn ) select * from
tmp order by 2 desc fetch first 1 row only

NAME RN
-------------------------------------------------- -----------
SQL0347W The recursive common table expression "AKALICKI.TMP" may
contain an
infinite loop. SQLSTATE=01605

VS000_001_tVS001_002_tVS002_003_r 3

1 record(s) selected with 1 warning messages printed.
Cheers
Adrian

Nov 12 '05 #5

P: n/a
"adik_q" <ad****@wp.pl> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com
Hi,
WITH is not available in V7 zOS, only in V8.
Row_number() fuction as well is not there on zOS.

It's a pitty. This really nice features.


Yeah !!! ..it's a pitty :( :(

Thanks for the answer

Regards
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Nov 12 '05 #6

P: n/a
Thanks you very much.

We will try it as soon as possible. As I explain in other post to
Juliane, I think it's going to work, but now I'm not sure if our dev.
enviroment will support this kind of SQL. I think yes, but dev. team
it's not sure

Best Regards
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Nov 12 '05 #7

P: n/a
First of all, thank you for your interest ;)
May, what you are asking is about how to present your resultset,
especially when it is only one column.
Well, the facts are:
- We’re developing an integration process from flat text file into DB2
database.
- We’re using the DataStage tool (ETL) one.
- At some stage, we need to create from several rows in that table
(well a complex one, but similar to the example ;) ) a unique name as
explained before.
This usually is done outside SQL. Are u using a tool ? I not, then a
simple rexx script will do the job of changing it to one line.


We think we can query the table as Tonkuma has explained in a ODBC
connector and then get the result set (one row with only one column) to
use it later.

Well, our team thinks that with only one query we can solve our
problems. Be sure I will keep this thread alive and updated.

Thanks a lot everybody

Best regards

May
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Nov 12 '05 #8

P: n/a
Why do you want to violate First Normal Form? Why are smarter than Dr.
Codd and 30+ years of RDBMS theory?

In a tiered architecture, display and fomatting is done in the front
end and NEVER in the database. This is far more fundamental than just
SQL.

There are highly proprietary ways to do this (aka KLUDGES) using
cursors and other tricks.

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.