Re: SQL rows to columns

This problem is generally known as pivoting rows to columns - in Excel there is a copy/paste option for that, and SQL Serer has a PIVOT.


But a Google search for "convert rows to columns in sql without pivot" is a place to find several examples - mostly they involve using a CASE() in a correlated table expression (CTE), such as Birgitta presented.

Cheers
Vern


On Tue, 12 Mar, 2024 at 4:37 PM, Niels Liisberg <nli@xxxxxxxxxxxxxxxxx> wrote:


To: midrange systems technical discussion

If you could use one column then:

Select … , ( select listagg(xxx) from …)
From ….

tirs. 12. mar. 2024 kl. 21.47<http://21.47> skrev Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx<mailto:Hauser@xxxxxxxxxxxxxxx>

:

You may try something like this (it is at least one Sub-Select less)

With x as (Select Ucntk, Min(Case When UCq3# = 1 Then UCCMT End) Comment1,
Min(Case When UCq3# = 2 Then UCCMT End) Comment2
from Notepduc
Where UCNOTT = 'C'
and UCSQ3 in (1, 2)
Group By UCNTK
Select Odcom#, Odord#, Odcmt#,
Coalesce(Comment1, '') Comment1, Coalesce(Comment2m '') Comment2
from Oeorlod Left join x on ucntk = Odcmt#

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Greg
Wilburn
Sent: Tuesday, 12 March 2024 19:46
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: SQL rows to columns

Wondering if there a more "elegant" solution to my query?

I have an order lines table (OEORDLOD) and a notes table (NOTEPDUC).
For each row in the order lines table, there can be up to two rows in the
notes table (sequence 1 and sequence 2).
The key is ODCMT# for order lines and UCNTK for Notes.

I'm trying to create one row that includes the comments in two columns

My current SQL statement looks like this:

select odcom#, odord#, odcmt#,
coalesce((select uccmt from notepduc
where ucnott = 'C' and ucntk = odcmt# and
ucsq3# = 1),'') as comment1,
coalesce((select uccmt from notepduc
where ucnott = 'C' and ucntk = odcmt# and
ucsq3# = 2),'') as commen2 from oeordlod where
odord# = '10574107'

I have a similar scenario with the Order Header, but it can have up to 5
notes lines.
[Logo]<https://www.totalbizfulfillment.com/><https://www.totalbizfulfillment.com/>> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx><mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><http://www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.