I have data as following

STORE_NO    STORE_ADDRESS   STORE_TYPE  STORE_OWNER STORE_HOURS
1           123             Drive Thru  Harpo       24hrs
1           123             Curbside    Harpo       24hrs
1           123             Counter     Harpo       24hrs
2           456             Drive Thru  Groucho     9 to 9
2           456             Counter     Groucho     9 to 9

And I want to pivot it as following.

STORE_NO    STORE_ADDRESS   Drive Thru  Curbside    Counter STORE_OWNER STORE_HOURS
1           123             TRUE        TRUE        TRUE    Harpo       24hrs
2           456             TRUE        FALSE       TRUE    Groucho     9 to 9

Here is what I have

select * 
  from stores 
    pivot(count(STORE_TYPE) for STORE_TYPE in ('Drive Thru', 'Curbside', 'Counter'))
as store_flattened;

But this returns a 1 or a 0. How do I convert to TRUE / FALSE without making this a CTE?

CodePudding user response：

If you are ok with putting column names rather then select *, then following can be used -

select STORE_NO,STORE_ADDRESS,STORE_OWNER,STORE_HOURS,
    "'Drive Thru'"=1 as drivethru, 
    "'Curbside'"=1 as curbside, 
    "'Counter'"=1 as counter
from stores
    pivot(count(STORE_TYPE) for STORE_TYPE in ('Drive Thru', 'Curbside', 'Counter'))
as store_flattened;

CodePudding user response：

I honestly think you should leave it as is. Any attempt at a workaround will result in either complicating the pivot logic or having to manually specify the columns names in multiple places; especially with pivoted columns appearing before the rest. Having said that, if you must find a way to do this, here is an attempt.

I know you wanted to avoid a CTE, but I am using it for a purpose different than what you might had in mind. General idea in steps--

1. In a CTE, sub-select some of the columns you want to appear before the pivoted columns. Create a flag based on whether store_type (b.value) from the lateral flatten matches existing store_type for a given row. You'll notice the values passed to input=> can be easily copy-pasted to the pivot clause

2. Pivot using max(flag) which will turn (false,true)->true and (false,false)->false. You can run the CTE portion to see why that matters and how it solves the main issue

3. Finally, use a natural join with the main table to append the rest of the columns (this is the first time I found a natural join useful enough to keep it. I actively avoid them otherwise)

with cte (store_no, store_address, store_type, flag) as

(select store_no, store_address, b.value::string, b.value::string = store_type
 from t, lateral flatten(input=>['Drive Thru', 'Curbside', 'Counter']) b) 

select *
from cte pivot(max(flag) for store_type in ('Drive Thru', 'Curbside', 'Counter'))
natural join (select distinct store_no, store_owner, store_hours from t)

Outputs: