× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




I'm still playing with LATERAL, and having some great results for
simplifying what used to be complicated looking queries. I had a thought
about using them to pivot data in a table, and what I'm doing here seems to
work great, but it looks kind of funny to join the table on 1=1, because
"left outer join" requires something. Whenever I have to do something
strange like that, it usually means I'm doing it wrong. Should I be
writing it another way?


CIC is a table with part and facility, named ICPROD and ICFAC respectively:


select ICPROD, t1.ICFAC, t2.icfac
from (select distinct ICPROD from CIC) CIC
left outer join
lateral(select ICFAC from CIC b
where ICPROD=CIC.ICPROD
fetch first row only) t1
on 1=1
left outer join
lateral(select ICFAC from CIC b
where ICPROD=CIC.ICPROD
and ICFAC not in (t1.icfac)
fetch first row only) t2
on 1=1



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.