Is there a way to Union different tables in the same way Tableau is able to?

Question

question

Lea90SA asked Jun 24, '19

Is there a way to Union different tables in the same way Tableau is able to?

Forgive me if this seems like an arbitrary question as I am fairly new to SQL but have done as much research as I can.

We use SQL databases as a datasource in Tableau. The problem is that we're trying to replicate a function that Tableau offers, but inside of SQL itself. What Tableau is able to do is "union" tables directly on top of one another, even if the columns are not all equal or of the same type. It then simply places nulls inside cells that don't have corresponding values in any of the tables. Tableau then lets you 'pivot' selected columns from the newly unioned table. However, Tableau only lets you do this using Excel and similar files, but not SQL tables.

So my dilemma is I'm trying to discern whether there is a way (even if it's a complicated way) to achieve similar results in SQL itself, first.

So if we took the 3 example tables below, we'd want to union them in SQL like this:

And then we'd want to pivot the "Sales", "Refunds", "Complaints" and "Customers" columns into a single column, like this:

Again, I'm new to SQL but I can't imagine why this would be so difficult to achieve? But wherever I've researched this, people say it's almost impossible to do so?

sql sql-server pivot union

desired-union.png (35.6 KiB)

desired-pivot.png (34.7 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-06-25T07:18:40Z

Kev Riley answered Jun 25, '19

For the example you have given, you can UNION the tables together, then UNPIVOT them to get the final result

declare @Table1 table ([Date] date, Company varchar(50), Sales int)
insert into @Table1 select '1 Jun 2019', 'Example 1', 13
insert into @Table1 select '1 Jun 2019', 'Example 1', 57

declare @Table2 table ([Date] date, Company varchar(50), Refunds int)
insert into @Table2 select '5 Jun 2019', 'Example 3', 27
insert into @Table2 select '7 Jun 2019', 'Example 4', 33

declare @Table3 table ([Date] date, Company varchar(50), Complaints int, Customers int)
insert into @Table3 select '11 Jun 2019', 'Example 5', 3, 13
insert into @Table3 select '13 Jun 2019', 'Example 6', 5, 17
insert into @Table3 select '13 Jun 2019', 'Example 7', 4, 14

select Date, Company, Sales, null as Refunds, null as Complaints, null as Customers from @Table1
union all
select Date, Company, null as Sales, Refunds, null as Complaints, null as Customers from @Table2
union all
select Date, Company, null as Sales, null as Refunds, Complaints, Customers from @Table3

This shows the UNIONed data

Date       Company                                            Sales       Refunds     Complaints  Customers
---------- -------------------------------------------------- ----------- ----------- ----------- -----------
2019-06-01 Example 1                                          13          NULL        NULL        NULL
2019-06-01 Example 1                                          57          NULL        NULL        NULL
2019-06-05 Example 3                                          NULL        27          NULL        NULL
2019-06-07 Example 4                                          NULL        33          NULL        NULL
2019-06-11 Example 5                                          NULL        NULL        3           13
2019-06-13 Example 6                                          NULL        NULL        5           17
2019-06-13 Example 7                                          NULL        NULL        4           14

(7 rows affected)

Then use that data as the source of an UNPIVOT - however the null values will be excluded from the results, so I had to replace the nulls with '0', then set them back to null with nullif() - this may or may not work in your case - it depends on your data and whether or not 0 is a valid value

select Date, Company, Measure, nullif([Value],0)
from (
    select Date, Company, Sales, 0 as Refunds, 0 as Complaints, 0 as Customers from @Table1
    union all
    select Date, Company, 0 as Sales, Refunds, 0 as Complaints, 0 as Customers from @Table2
    union all
    select Date, Company, 0 as Sales, 0 as Refunds, Complaints, Customers from @Table3
    ) UnionedData
unpivot
 ([Value] for Measure in (Sales, Refunds, Complaints, Customers)) as upvt;

Date       Company       Measure         
---------- ------------- --------------- -----------
2019-06-01 Example 1     Sales           13
2019-06-01 Example 1     Refunds         NULL
2019-06-01 Example 1     Complaints      NULL
2019-06-01 Example 1     Customers       NULL
2019-06-01 Example 1     Sales           57
2019-06-01 Example 1     Refunds         NULL
2019-06-01 Example 1     Complaints      NULL
2019-06-01 Example 1     Customers       NULL
2019-06-05 Example 3     Sales           NULL
2019-06-05 Example 3     Refunds         27
2019-06-05 Example 3     Complaints      NULL
2019-06-05 Example 3     Customers       NULL
2019-06-07 Example 4     Sales           NULL
2019-06-07 Example 4     Refunds         33
2019-06-07 Example 4     Complaints      NULL
2019-06-07 Example 4     Customers       NULL
2019-06-11 Example 5     Sales           NULL
2019-06-11 Example 5     Refunds         NULL
2019-06-11 Example 5     Complaints      3
2019-06-11 Example 5     Customers       13
2019-06-13 Example 6     Sales           NULL
2019-06-13 Example 6     Refunds         NULL
2019-06-13 Example 6     Complaints      5
2019-06-13 Example 6     Customers       17
2019-06-15 Example 7     Sales           NULL
2019-06-15 Example 7     Refunds         NULL
2019-06-15 Example 7     Complaints      4
2019-06-15 Example 7     Customers       14

(28 rows affected)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lea90SA commented · Jun 25, 2019 at 07:24 AM

Wow this is brilliant, thank you so much! This is excellent - thanks for providing the code as well.

0 ·

Answer 2 · 2019-06-24T13:52:20Z

Jon Crawford answered Jun 24, '19

You are describing LEFT JOINing the tables together on date and company, then using a PIVOT on Measure. Not impossible to do.

However, if you're trying to build a function that allows for joining together any two objects arbitrarily, and pivots those, you will have a harder time. In that case you will want to look at dynamic pivots, and you would either have to pre-define for the function how to join different tables together or require that column names matched up or something similar.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lea90SA commented · Jun 25, 2019 at 06:21 AM

Thank you!

0 ·

question