Re: SQL rows to columns

[Bryan Dietz <bdietz400@xxxxxxxxx>]

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