Create view which contains multiple list columns

Question

I have searched high and low but can't seem to find an answer.

I want to create a status table which contains multiple lists from 3 different tables.

for example,

table 1 distinct of customer_status column table 2 distinct of company_status column table 3 distinct of product_status column

There is no direct link between any of the tables.

I want to end up with a table that looks like below.

id	customer_status	company_status	product_status
1	Closed	New	Obsolete
2	Open	Opportunity	Branded
3	Pending		Own Brand
4	On Stop

I have tried using unions but that creates duplicates & I can't use joins as there is nothing to join to between the tables.

Answer 1

Option 1

1. Combine into a single table
2. Add a row number per source
3. Self-join on row number

I think it will work.

WITH statuses AS

(
    SELECT DISTINCT customer_status AS status_value, 'customer' AS status_type
    FROM table1
    UNION
    SELECT DISTINCT company_status, 'company'
    FROM table2
    UNION
    SELECT DISTINCT product_status, 'product'
    FROM table3
),

rownumbers AS
(
    SELECT status_type, 
        status_value, 
        ROW_NUMBER() OVER (PARTITION BY status_type 
                            ORDER BY status_value) AS id
    FROM statuses
),
result AS 
(
SELECT COALESCE(x.id,y.id,z.id) AS id,
    x.status_value AS customer_status,
    y.status_value AS company_status,
    z.status_value AS product_status
FROM (SELECT id, status_value FROM rownumbers WHERE status_type = 'customer') AS x
    FULL OUTER JOIN (SELECT id, status_value FROM rownumbers WHERE status_type = 'company') AS y ON x.id = y.id
    FULL OUTER JOIN (SELECT id, status_value FROM rownumbers WHERE status_type = 'product') AS z ON x.id = z.id
)
SELECT * FROM result;

Option 2

A) For each table:

1. Get distinct
2. Add row number

B) Join on row number

Probably slightly shorter and simpler than option 1.

WITH
distinct1 AS
(SELECT DISTINCT customer_status FROM table1),
id1 AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY customer_status) AS id FROM distinct1),
distinct2 AS
(SELECT DISTINCT company_status FROM table2),
id2 AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY company_status) AS id FROM distinct2),
distinct3 AS
(SELECT DISTINCT product_status FROM table3),
id3 AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY product_status) AS id FROM distinct3),
result AS 
(
SELECT COALESCE(x.id,y.id,z.id) AS id,
    customer_status,
    company_status,
    product_status
FROM id1 AS x
    FULL OUTER JOIN id2 AS y ON x.id = y.id
    FULL OUTER JOIN id3 AS z ON x.id = z.id
)
SELECT * FROM result;