|
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,posting. Got better results with your search criteria
Thanks for the google advice... I was searching for PIVOT in before
Wondering why don’t we have pivot?has indexes advised.
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
I also tried moving the WHERE clause from the CTE to the select...performance seemed to be the same.
indexes, are there other methods I should consider for speed?
This will be used quite a bit. Other than creating the suggested
VERNON HAMBERG Owner via MIDRANGE-L
Thanks all!!
Greg
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of
Sent: Wednesday, March 13, 2024 11:22 AMmidrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx<mailto:vhamberg@xxxxxxxxxxxxxxx>>;
Subject: Re: SQL rows to columnsthere is a copy/paste option for that, and SQL Serer has a PIVOT.
This problem is generally known as pivoting rows to columns - in Excel
is a place to find several examples - mostly they involve using a CASE() in
But a Google search for "convert rows to columns in sql without pivot"
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<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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.