Re: SQL rows to columns

great time to ask for an "idea"
https://ideas.ibm.com/subscriptions?category=7070617969592295165
Bryan

Greg Wilburn wrote on 3/13/2024 11:00 AM:

Vern,

Thanks for the google advice... I was searching for PIVOT in before

posting. Got better results with your search criteria

Wondering why don’t we have pivot?

Anyway.. this is what I came up:

With notes as
(select ucnott, ucntk, ucnot#,
max(case when ucsq3#=1 then uccmt else '' end) as cmt1,
max(case when ucsq3#=2 then uccmt else '' end) as cmt2
from notepduc
where ucridc='UC' and ucnott='C' and ucnot#=1
group by ucnott, ucntk, ucnot#)

select odcom#, odord#, odprt#, cmt1, cmt2
from oeordlod
join notes on ucntk=odcmt#
where odord# = '10574107';

This was just as quick as my original sub-select statement (~50ms), but

has indexes advised.

I also tried moving the WHERE clause from the CTE to the select...

performance seemed to be the same.

This will be used quite a bit. Other than creating the suggested

indexes, are there other methods I should consider for speed?

Thanks all!!
Greg


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

VERNON HAMBERG Owner via MIDRANGE-L

Sent: Wednesday, March 13, 2024 11:22 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>;

midrange-l@xxxxxxxxxxxxxxxxxx

Subject: 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

--

-- .
Bryan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: 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
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.