473,796 Members | 2,628 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_tVS0 01_002_tVS002_0 03_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 17708
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||ch ild.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_0 01_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||ch ild.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.T MP" may
contain an
infinite loop. SQLSTATE=01605

VS000_001_tVS00 1_002_tVS002_00 3_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.goo glegroups.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
10502
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 looks neat again? something like: LIST = , , , , , , , , ]
8
2087
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) What does this mean? Cannot functions be large? Or is it simply an advice that functions should be small and simple?
2
1737
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 accomplished by putting everything in a Frame(), using width, height, grid_propagate(0) , and a scrollbar? or do I have to grid 5 rows at a time? If the latter, can I just grid over the previous 5 or do they have to be explicitly removed...
5
1362
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 people only sent forms with one or two lines, I didn't mind making a simple parameter query that searched for a serial number (ESN) and showed me the relevant line of my office's tracking database so I could change the date, etc. Here's how it...
3
2010
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
3090
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 . = DateSerial(Year(Date()),1,30), . = False, . = DateSerial(Year(Date()),2,28), . = False " & _ "WHERE (((.JanPaymentRec)= True) " & _
0
942
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. Here is an example: 2007-01-17;Gutschrift VESR;510;69370.00;2007-01-17
4
2388
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 example, CTRL-G. It would allow the user to input several lines. Thanks Julien
2
3380
by: arungkumar | last post by:
Can a string literal extend over several lines? why or why not?
0
9680
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9528
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10455
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10173
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10006
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5441
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5573
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3731
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2925
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.