asked on

PostgreSQL/ Redshift - Append (Pivot) Distinct Values

Hello there!

I made a SQL Fiddle thingy here:
https://www.db-fiddle.com/f/5UxJHyAaLTuTQEzCxnQf7W/1

But here is the DDL:

CREATE TABLE Table1 (
  	 MyID int
  	,MyDate date
);

CREATE TABLE Table2 (
  	 MyID int
  	,MyDate date
  	,Item varchar(20)
);

INSERT INTO Table1 (MyID, MyDate) VALUES 
(1, '2020-01-01'),
(2, '2020-01-02'),
(3, '2020-01-03'),
(4, '2020-01-03');

INSERT INTO Table2 (MyID, MyDate, Item) VALUES 
(1, '2020-01-01', 'Widget 0'),
(1, '2020-01-01', 'Widget 1'),
(1, '2020-01-01', 'Widget 1'),
(2, '2020-01-02', 'Widget 1'),
(3, '2020-01-03', 'Widget 0'),
(3, '2020-01-03', 'Widget 10'),
(3, '2020-01-03', 'Widget 2'),
(3, '2020-01-03', 'Widget 7'),
(4, '2020-01-03', ''),
(4, '2020-01-03', 'Widget 87');

Open in new window

What I'm trying to do is this:
Joining Table1 and Table2 on MyID and MyDate, generate SQL to create a result set that includes the MyId, MyDate, and distinct Item values for the MyID and MyDate values in Table2 as columns (Item1 - Item#).
The count of distinct Item values for each MyID and MyDate in Table2 will be unknown and so the number of Item# columns should be generated dynamically based on the max count of distinct values for any MyID and MyDate.

For example - using the sample data the row for MyID=3 and MyDate=2020-01-03 would look like this:
----------------------------------------------------------------------------------
MyID | MyDate | Item1 | Item2 | Item3 | Item4 |
----------------------------------------------------------------------------------
3 | 2020-01-03 | Widget 0 | Widget 2 | Widget 7 | Widget 10 |
----------------------------------------------------------------------------------

I did this manually using LISTAGG(Item, ', ') as All_Items and then breaking them back out with multiple SPLIT_PART(All_Items, ',', #) statements but I'm thinking there's a much more graceful way to do this (e.g., PIVOT) but I'm too dense to work it out. :P

Thanks in advance for any help!

slightwv (䄆 Netminder)

You have to know the maximum number of items allowed.

Try this:

with distinct_items as (
   select distinct myid, mydate, item
   from table2
   where item is not null and item != ''
),
base as (
   select myid, mydate, item,
      row_number() over(partition by myid, mydate order by myid,mydate) rn
   from distinct_items di
      join table1 using(myid,mydate)
)
select myid, mydate,
   max(case when rn=1 then item end) item1,
   max(case when rn=2 then item end) item2,
   max(case when rn=3 then item end) item3,
   max(case when rn=4 then item end) item4,
   max(case when rn=5 then item end) item5,
   max(case when rn=6 then item end) item6
from base
group by myid, mydate
order by myid, mydate
;

Open in new window

My fiddle here (I prefer this site):

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e9e83de4901bebb5ea548151eb7a3fc0

ttist25

ASKER

Thanks for this @slightwv.

Part of what I'm hoping to accomplish for this solution is to dynamically generate the Item# columns.

I'm wondering if something like this could will work:
https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial