473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

How to concat several lines into one

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
8 17622
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
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
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
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
"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: Dominik Kaspar | last post by:
i want to initialize a constant list at the beginning of a python program. but the list is too big for one line. is there any possibility to spread the list over several lines, so that the code...
8
by: Frans Englich | last post by:
Hello, I take PyChecker partly as an recommender of good coding practice, but I cannot make sense of some of the messages. For example: runner.py:878: Function (main) has too many lines (201)...
2
by: William Gill | last post by:
I need to display a couple of labels and a checkbox from each entry in my database. Simple enough, but there are several hundred records, and I only want to display 5 or 10 at a time. Can this be...
5
by: KitKat | last post by:
Right now at work, I get forms e-mailed to me in an Excel chart with a serial number in one column and a new expiration date for a device's trial period in another column. For a while, when...
3
by: casul | last post by:
Hi All, I was told there were a few macro gurus on this group :) I'm trying to define a macro that will allow me to write the following code : #include MY_MACRO( NAME, SPACE )
2
by: Shokoth | last post by:
I also wanted to know how to spread out sql statement over 3 lines. I am writing an sql update statment in VB. I have about 12 fields to update. strSQL = "UPDATE " & _ "Set . =...
0
by: Pirmin | last post by:
How can I find the matches for bookings that meet the following conditions. A booking always starts with a date. A booking can consist of several lines. Dates can be part of the booking text. ...
4
by: TP | last post by:
Hi everybody, When using raw_input(), the input of the user ends when he types Return on his keyboard. How can I change this behavior, so that another action is needed to stop the input? For...
2
by: arungkumar | last post by:
Can a string literal extend over several lines? why or why not?
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.