sql to convert rows to column

Ron Thomas

sql to convert rows to column

Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.

 

Regards

Ron T

 

Philip Sevetson

sql to convert rows to column
(in response to Ron Thomas)
Ron,

Look for “Recursive” SQL in the archive. There’s something in there somewhere where they did this a couple of years ago.

-phil (sevetson)


From: Ron Thomas <[login to unmask email]>
Sent: Tuesday, August 13, 2019 9:09 AM
To: [login to unmask email]
Subject: [DB2-L] - sql to convert rows to column


Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.



Regards

Ron T



-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

sql to convert rows to column
(in response to Philip Sevetson)
Ron,

Specifically, check out this link: https://www.idug.org/p/fo/st/topic=19&post=144489#p144489
You’ll have to log in at idug to see it.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil <[login to unmask email]>
Sent: Tuesday, August 13, 2019 9:12 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column

Ron,

Look for “Recursive” SQL in the archive. There’s something in there somewhere where they did this a couple of years ago.

-phil (sevetson)


From: Ron Thomas <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, August 13, 2019 9:09 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - sql to convert rows to column


Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.



Regards

Ron T



-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

David Simpson

sql to convert rows to column
(in response to Ron Thomas)
The LISTAGG function does exactly this. If you are using Db2 for z/OS you need to be on Version 12 at function level V12R1M501 or higher. Not sure about Db2 LUW.

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m501.html

You may also need to wrap your current query in a table expression then GROUP BY the second column.

From: Ron Thomas <[login to unmask email]>
Sent: Tuesday, August 13, 2019 8:09 AM
To: [login to unmask email]
Subject: [DB2-L] - sql to convert rows to column


Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.



Regards

Ron T



-----End Original Message-----

Phil Grainger

sql to convert rows to column
(in response to Philip Sevetson)
LISTAGG()??

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Sevetson, Phil <[login to unmask email]>
Sent: 13 August 2019 14:12
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: sql to convert rows to column

Ron,

Look for “Recursive” SQL in the archive. There’s something in there somewhere where they did this a couple of years ago.

-phil (sevetson)


From: Ron Thomas <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, August 13, 2019 9:09 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - sql to convert rows to column


Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.



Regards

Ron T



-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

David Simpson

sql to convert rows to column
(in response to David Simpson)
Correction... GROUP BY the first column

On Aug 13, 2019 8:30 AM, David Simpson <[login to unmask email]> wrote:
The LISTAGG function does exactly this. If you are using Db2 for z/OS you need to be on Version 12 at function level V12R1M501 or higher. Not sure about Db2 LUW.

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m501.html

You may also need to wrap your current query in a table expression then GROUP BY the second column.

From: Ron Thomas <[login to unmask email]>
Sent: Tuesday, August 13, 2019 8:09 AM
To: [login to unmask email]
Subject: [DB2-L] - sql to convert rows to column


Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.



Regards

Ron T



-----End Original Message-----

-----End Original Message-----

Philip Sevetson

sql to convert rows to column
(in response to David Simpson)
Well, shoot. V11 here.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: David Simpson <[login to unmask email]>
Sent: Tuesday, August 13, 2019 9:25 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column

The LISTAGG function does exactly this. If you are using Db2 for z/OS you need to be on Version 12 at function level V12R1M501 or higher. Not sure about Db2 LUW.

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m501.html

You may also need to wrap your current query in a table expression then GROUP BY the second column.

From: Ron Thomas <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, August 13, 2019 8:09 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - sql to convert rows to column


Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.



Regards

Ron T



-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Isaac Yassin

sql to convert rows to column
(in response to Ron Thomas)
Hi,
You can use LISTAGG in Db2 12.

*Isaac Yassin *
*IBM Gold Consultant*
*IBM Champion for Analytics #ibmchampion*
IBM Certified Solution Expert
IBM Certified System Administrator - DB2 for Z/OS 10, 11, 12
IBM Certified Database Administrator - DB2 for Z/OS 9, 10, 11
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair



On Tue, Aug 13, 2019 at 4:09 PM Ron Thomas <[login to unmask email]> wrote:

> Hi.
>
> I have the following query
>
> SELECT itm_nbr,
> CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE
> TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr =
> 102016
>
> So the output comes as
>
> itm _nbr trits
>
> 102016 1054O
> 102016 2836S
> 102016 26S
> 102016 6672S
> 102016 6673S
> 102016 290S
> 102016 292S
>
> So i need the o/p as below
>
> itm _nbr trt_no
> 102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S
>
> Is there a way we can do from a query ? if so how this can be done ?
>
> Thanks In Advance.
>
>
>
> Regards
>
> Ron T
>
>
>
> -----End Original Message-----
>

Philip Sevetson

sql to convert rows to column
(in response to Isaac Yassin)
For those of us not yet on DB2 V12 z/OS, there is an alternative (thank you to the wizard who posted this on StackOverflow):

/* CTE to set up the test data for this */
WITH A1 (ID, text)
AS (
SELECT 1, 'AAA' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 1, 'BBB' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 1, 'CCC' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 2, 'DDD' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 2, 'EEE' FROM SYSIBM.SYSDUMMY1
UNION
)

SELECT ID,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',text))) as CLOB(32K)), 3)
FROM A1
GROUP BY ID;

The results display this way:

ID 2
-- -------------
1 AAA, BBB, CCC
2 DDD, EEE


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Isaac Yassin <[login to unmask email]>
Sent: Tuesday, August 13, 2019 10:07 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column

Hi,
You can use LISTAGG in Db2 12.

Isaac Yassin
IBM Gold Consultant
IBM Champion for Analytics #ibmchampion
IBM Certified Solution Expert
IBM Certified System Administrator - DB2 for Z/OS 10, 11, 12
IBM Certified Database Administrator - DB2 for Z/OS 9, 10, 11
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair
[https://docs.google.com/uc?export=download&id=1QIrjKZDPVU3Q6kSLESR3M4TWHIoS-T8q&revid=0B1Xy7lNi3nb8djBvaUErQlRFOE5UcGF5YlliL1NzcHdFSXVjPQ]


On Tue, Aug 13, 2019 at 4:09 PM Ron Thomas <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hi.

I have the following query

SELECT itm_nbr,
CASE WHEN TRT.SEND_OMIT_CODE = 'O' THEN TRT.TRT_NBR||'O' ELSE TRT.TRT_NBR||'S' END AS "TRITS" FROM MX003.IRTM_TRT trt where itm_nbr = 102016

So the output comes as

itm _nbr trits

102016 1054O
102016 2836S
102016 26S
102016 6672S
102016 6673S
102016 290S
102016 292S

So i need the o/p as below

itm _nbr trt_no
102016 1054O ,2836S ,26S ,6672S ,6673S ,290S ,292S

Is there a way we can do from a query ? if so how this can be done ?

Thanks In Advance.



Regards

Ron T



-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Ron Thomas

RE: sql to convert rows to column
(in response to Philip Sevetson)

Ok. we are DB2 VERSION 11 and when i got executed i am seeing as below

102016 <LOB locator>

Kindly let me know how to see the data in the 2'nd column ?

 

Regards

Ron T

Philip Sevetson

sql to convert rows to column
(in response to Ron Thomas)
Ron,

What code did you run to get this?

-phil

From: Ron Thomas <[login to unmask email]>
Sent: Tuesday, August 13, 2019 12:13 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column


Ok. we are DB2 VERSION 11 and when i got executed i am seeing as below

102016

Kindly let me know how to see the data in the 2'nd column ?



Regards

Ron T

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Ron Thomas

RE: sql to convert rows to column
(in response to Philip Sevetson)

Ok. Here is he code that i ran


SELECT ITEM_NBR,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',trait_nbr))) as CLOB(32K)), 3)
FROM k3003.ITEM_TRAIT where item_nbr = 102016
GROUP BY ITEM_NBR
FETCH FIRST 10 ROWS ONLY WITH UR;

 

Thanks

Ron T

 

Philip Sevetson

sql to convert rows to column
(in response to Ron Thomas)
Ron,
Here’s what I ran and my results.

WITH A1 (item_nbr, trait_nbr)
AS (
SELECT 102016, '10540' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 102016, '2836S' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 102016, '26S' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 102016, '6672S' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 102016, '6673S' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 102016, '290S' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 102016, '292S' FROM SYSIBM.SYSDUMMY1
)

SELECT item_nbr,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',trait_nbr))) as CLOB(32K)), 3)
FROM A1 where item_nbr = 102016
GROUP BY item_nbr
FETCH FIRST 10 ROWS ONLY WITH UR
;

ITEM_NBR 2
-------- -------------------------------------------
102016 10540, 26S, 2836S, 290S, 292S, 6672S, 6673S

I don’t have a permanent table built for this. I’m not sure why that would make a difference. Try running the above code as-is, including the inline table (CTE), and see if you get results.
-phil



Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Ron Thomas <[login to unmask email]>
Sent: Tuesday, August 13, 2019 2:11 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column


Ok. Here is he code that i ran

SELECT ITEM_NBR,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',trait_nbr))) as CLOB(32K)), 3)
FROM k3003.ITEM_TRAIT where item_nbr = 102016
GROUP BY ITEM_NBR
FETCH FIRST 10 ROWS ONLY WITH UR;



Thanks

Ron T



-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Horacio Villa

sql to convert rows to column
(in response to Philip Sevetson)
I adapted the sql to query a table I have and is working fine.
Thanks.
Horacio

Ron Thomas

RE: sql to convert rows to column
(in response to Horacio Villa)

Thanks  a lot Philip , it is working now . Earlier i was running in QMF for windows and i am not sure why it is not working there . Now in Mainframe i executed and worked good.  

Thanks

Ron T

Philip Sevetson

sql to convert rows to column
(in response to Ron Thomas)
:: wild cheers ::
Okay! We have a new piece of working code!!!

-phil

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Ron Thomas <[login to unmask email]>
Sent: Tuesday, August 13, 2019 4:45 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column


Thanks a lot Philip , it is working now . Earlier i was running in QMF for windows and i am not sure why it is not working there . Now in Mainframe i executed and worked good.

Thanks

Ron T

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Ron Thomas

RE: sql to convert rows to column
(in response to Philip Sevetson)

Philip.   

 

The below SQL is not working when i am putting the case statement , it is throwing SQLCODE - 109

 

SELECT ITEM_NBR,
SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT( ', ', CASE WHEN SEND_OMIT_CODE = 'O' THEN TRAIT_NBR||'O' ELSE TRAIT_NBR||'S' END AS TRAIT_NBR))) AS CLOB(32K)),3) AS trait_nbr
FROM GRS003.ITEM_TRAIT WHERE ITEM_NBR = 109016 GROUP BY ITEM_NBR

 

Where as the below one is working .

SELECT ITEM_NBR,
SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT( ', ', TRAIT_NBR))) AS
CLOB(32K)),3) AS trait_nbr
FROM B3MDS003.ITEM_TRAIT WHERE ITEM_NBR = 102016
GROUP BY ITEM_NBR

 

I need to put the CASE statement , is there a way this can be done ?

 

Thanks

Ron T

Philip Sevetson

sql to convert rows to column
(in response to Ron Thomas)
Ron,

Take the “AS TRAIT_NBR” out of your embedded CASE statement.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Ron Thomas <[login to unmask email]>
Sent: Thursday, August 15, 2019 9:13 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column


Philip.



The below SQL is not working when i am putting the case statement , it is throwing SQLCODE - 109



SELECT ITEM_NBR,
SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT( ', ', CASE WHEN SEND_OMIT_CODE = 'O' THEN TRAIT_NBR||'O' ELSE TRAIT_NBR||'S' END AS TRAIT_NBR))) AS CLOB(32K)),3) AS trait_nbr
FROM GRS003.ITEM_TRAIT WHERE ITEM_NBR = 109016 GROUP BY ITEM_NBR



Where as the below one is working .

SELECT ITEM_NBR,
SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT( ', ', TRAIT_NBR))) AS
CLOB(32K)),3) AS trait_nbr
FROM B3MDS003.ITEM_TRAIT WHERE ITEM_NBR = 102016
GROUP BY ITEM_NBR



I need to put the CASE statement , is there a way this can be done ?



Thanks

Ron T

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Ron Thomas

RE: sql to convert rows to column
(in response to Philip Sevetson)

Thanks a lot Philip , it worked .

alain pary

RE: sql to convert rows to column
(in response to Ron Thomas)

Hello, 

if you are in version 11 , you can use XMLagg and make some cleaning  in the output 

here is a sample 

SELECT TBNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME, '')
ORDER BY CO.COLNO)) AS VARCHAR(2000)),'<C>',','),'</C>','')) AS COLLIST
FROM SYSIBM.SYSCOLUMNS CO
WHERE TBCREATOR = 'SYSIBM'
AND COLTYPE ^= 'ROWID'
AND TBNAME = 'SYSTABLES'
GROUP BY TBNAME

 

I hope it help,

 

regards 

 

Alain

Philip Sevetson

sql to convert rows to column
(in response to alain pary)
Alain,

Can you run that code in your system and let me know if you’re getting the same error as I am seeing? I can’t parse it in my head well enough to tell which tokens are causing this.

SELECT TBNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME, '')
ORDER BY CO.COLNO)) AS VARCHAR(2000)),'',','),'','')) AS COLLIST
FROM SYSIBM.SYSCOLUMNS CO
WHERE TBCREATOR = 'SYSIBM'
AND COLTYPE ^= 'ROWID'
AND TBNAME = 'SYSTABLES'
GROUP BY TBNAME
;


THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF REPLACE IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=3.69.56


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: alain pary <[login to unmask email]>
Sent: Friday, August 16, 2019 4:07 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column


Hello,

if you are in version 11 , you can use XMLagg and make some cleaning in the output

here is a sample

SELECT TBNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME, '')
ORDER BY CO.COLNO)) AS VARCHAR(2000)),'',','),'','')) AS COLLIST
FROM SYSIBM.SYSCOLUMNS CO
WHERE TBCREATOR = 'SYSIBM'
AND COLTYPE ^= 'ROWID'
AND TBNAME = 'SYSTABLES'
GROUP BY TBNAME



I hope it help,



regards



Alain

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

alain pary

RE: sql to convert rows to column
(in response to Philip Sevetson)

Hi phill, 

during the copy/paste some changes appears in my sql  

you must have VARCHAR(2000)),'<C>',','),'</C>','')) AS COLLIST  

ant the result is ok for V11 and V12

V12

NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK,REGENERATETS,KEYLABEL

V11
NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK

 

regards

Alain   

Phil Grainger

sql to convert rows to column
(in response to alain pary)
And I wonder what IS the easiest way to show the difference between two IMMEDIATLY adjacent SINGLE quotes – ‘’

As NOT being the same as one DOUBLE quote “

In the font I am using (Calibri) for eMail they are almost imperceptibly different (‘’ vs “)

But to Db2, there is a WORLD of difference

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: alain pary <[login to unmask email]>
Sent: 16 August 2019 13:44
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: sql to convert rows to column


Hi phill,

during the copy/paste some changes appears in my sql

you must have VARCHAR(2000)),'',','),'','')) AS COLLIST

ant the result is ok for V11 and V12

V12

NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK,REGENERATETS,KEYLABEL

V11
NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK



regards

Alain

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

Philip Sevetson

sql to convert rows to column
(in response to alain pary)
It’s not working for me. I’m currently guessing that the text which you send is getting corrupted in transit, presumably by DB2-L.

Let me try showing you my listing in Courier font. I did a copy-and-paste from your original email and executed it. Here’s the IBM Data Studio listing (DS 4.1.3, SQL RESULTS tab) at bottom.

My question is, is that _supposed_ to be adjacent apostrophes in the second argument of each of the REPLACE functions? Or is there a space or unprintable at your end which isn’t making it to me? If it works for you with two adjacent apostrophes, why isn’t DB2 flagging it as an empty string?

-phil

****

(courier font starts on next line)
SELECT TBNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME, '')
ORDER BY CO.COLNO)) AS VARCHAR(2000)),'',','),'','')) AS COLLIST
FROM SYSIBM.SYSCOLUMNS CO
WHERE TBCREATOR = 'SYSIBM'
AND COLTYPE ^= 'ROWID'
AND TBNAME = 'SYSTABLES'

THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF REPLACE IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=3.69.56

Script: \RemoteSystemsTempFiles\DDB1-SQL.sql
(courier font ends on previous line)



Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: alain pary <[login to unmask email]>
Sent: Friday, August 16, 2019 8:44 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column


Hi phill,

during the copy/paste some changes appears in my sql

you must have VARCHAR(2000)),'',','),'','')) AS COLLIST

ant the result is ok for V11 and V12

V12

NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK,REGENERATETS,KEYLABEL

V11
NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK



regards

Alain

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Philip Sevetson

sql to convert rows to column
(in response to Philip Sevetson)
Aha!

You’re sending < C > and < / C > in the REPLACE function. Since you don’t have spaces in it, DB2-L reads them and consumes them as HTML tags (as null tags) and doesn’t reproduce them. Your original SQL looks like:

SELECT TBNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME, '')
ORDER BY CO.COLNO)) AS VARCHAR(2000)),' < / C > ',','),' < C > ','')) AS COLLIST
FROM SYSIBM.SYSCOLUMNS CO
WHERE TBCREATOR = 'SYSIBM'
AND COLTYPE ^= 'ROWID'
AND TBNAME = 'SYSTABLES'
GROUP BY TBNAME
;

-- I have added spaces in the REPLACE second arguments to prevent the mail processor from consuming them as HTML.

-phil



Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil <[login to unmask email]>
Sent: Friday, August 16, 2019 9:01 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql to convert rows to column

It’s not working for me. I’m currently guessing that the text which you send is getting corrupted in transit, presumably by DB2-L.

Let me try showing you my listing in Courier font. I did a copy-and-paste from your original email and executed it. Here’s the IBM Data Studio listing (DS 4.1.3, SQL RESULTS tab) at bottom.

My question is, is that _supposed_ to be adjacent apostrophes in the second argument of each of the REPLACE functions? Or is there a space or unprintable at your end which isn’t making it to me? If it works for you with two adjacent apostrophes, why isn’t DB2 flagging it as an empty string?

-phil

****

(courier font starts on next line)
SELECT TBNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME, '')
ORDER BY CO.COLNO)) AS VARCHAR(2000)),'',','),'','')) AS COLLIST
FROM SYSIBM.SYSCOLUMNS CO
WHERE TBCREATOR = 'SYSIBM'
AND COLTYPE ^= 'ROWID'
AND TBNAME = 'SYSTABLES'

THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF REPLACE IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=3.69.56

Script: \RemoteSystemsTempFiles\DDB1-SQL.sql
(courier font ends on previous line)



Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: alain pary <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 16, 2019 8:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: sql to convert rows to column


Hi phill,

during the copy/paste some changes appears in my sql

you must have VARCHAR(2000)),'',','),'','')) AS COLLIST

ant the result is ok for V11 and V12

V12

NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK,REGENERATETS,KEYLABEL

V11
NAME,CREATOR,TYPE,DBNAME,TSNAME,DBID,OBID,COLCOUNT,EDPROC,VALPROC,CLUSTERTYPE,CLUSTERRID,CARD,NPAGES,PCTPAGES,IBMREQD,REMARKS,PARENTS,CHILDREN,KEYCOLUMNS,RECLENGTH,STATUS,KEYOBID,LABEL,CHECKFLAG,CHECKRID,AUDITING,CREATEDBY,LOCATION,TBCREATOR,TBNAME,CREATEDTS,ALTEREDTS,DATACAPTURE,RBA1,RBA2,PCTROWCOMP,STATSTIME,CHECKS,CARDF,CHECKRID5B,ENCODING_SCHEME,TABLESTATUS,NPAGESF,SPACEF,AVGROWLEN,RELCREATED,NUM_DEP_MQTS,VERSION,PARTKEYCOLNUM,SPLIT_ROWS,SECURITY_LABEL,OWNER,APPEND,OWNERTYPE,CONTROL,VERSIONING_SCHEMA,VERSIONING_TABLE,HASHKEYCOLUMNS,ARCHIVING_SCHEMA,ARCHIVING_TABLE,STATS_FEEDBACK



regards

Alain

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)