On Thu, Aug 18, 2016 at 10:56 AM, <hamann.w@t-online.de> wrote:
> I have a table cv with custid and vendid columns. Every entry represents the purchase of a product
> available from a specific vendor.
> Now, for a set of "interesting" vendors, I would like to select a new table
> custid, c415, c983, c1256
> based upon part queries
> select custid, count(vendid) as c415 from cv where vendid = 415 group by custid
....
Divide and conquer, first you get the raw data ( so you have what you
need as 'vertical' tagged columns ): ( beware, untested )...
with raw_data as (
select
custid, vendid, count(*) as c
from cv
where vendid in (415,983,1256)
group by 1,2;
)
Then put it in three columns ( transforming it into diagonal matrix ):
, column_data as (
select
custid,
case when vendid=415 then c else 0 end as c415,
case when vendid=983 then c else 0 end as c983,
case when vendid=1256 then c else 0 end as c1256
from raw_data
)
and then group then ( putting them into horizontal rows ):
select
custid,
max(c415) as c415,
max(c983) as c983,
max(c1256) as c1256
from column_data group by 1;
Note:
I used 0 in else to get correct counts for the case where not al
vendids are present. If you prefer null you can use it, IIRC max
ignores them.
Francisco Olarte.
