union type functionality required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

union type functionality required

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2015-03-09 : 08:30:34

i have the following data

TableT
Acc no, T1Total,T2Total,T3Total
1,10,10,10
2,20,20,20
3,30,30,30

TableA
Acc no, A1Total,A2Total,A3Total
1,40,40,40
7,50,50,40
8,60,60,60

I want the desired output to be

TableOutput
Acc no, T1Total,T2Total,T3Total, A1Total,A2Total,A3Total
1,10,10,10,0,0,0
2,20,20,20,0,0,0
3,30,30,30,0,0,0
1,0,0,0,40,40,40
7,0,00,50,50,50
8,0,0,0,60,60,60

Simple enough just use a UNION

SELECT Acc no, 0,0,0,A1Total,A2Total,A3Total
UNION ALL
SELECT Acc no, T1Total,T2Total,T3Total,0,0,0

However my solution was simplistic but I have many tables with many columns and I don't always want to put placeholder values columns in it. (nulls instead of zeros is also fine)
So I wold rather want to do something that just allows me to do something like this.
SELECT Acc no, A1Total,A2Total,A3Total
FANCY UNION
SELECT Acc no, T1Total,T2Total,T3Total

and it will now to return the output as
Acc no, A1Total,A2Total,A3Total,T1Total,T2Total,T3Total

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-09 : 13:30:32

Your FANCY UNION is called a JOIN

madlo
Starting Member

41 Posts

Posted - 2015-03-13 : 03:41:06

quote:
Originally posted by gbritton

Your FANCY UNION is called a JOIN

Not sure where my mind went.

I don't want to join the acc no. From my example the output should include two lines for acc 1
1,10,10,10,0,0,0
1,0,0,0,40,40,40

You are correct then the solution is a JOIN.

Here is the query

SELECT ISNULL(TableT.Acc,TableA.Acc) ,A1Total,A2Total,A3Total,T1Total,T2Total,T3Total
FROM TableT FULL OUTER JOIN TableA on 1=2

Since I need a join condition I use 1=2 (never occurs) but is there a way to write it neater it doesn't read very professional

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 08:49:47

CROSS JOIN

madlo
Starting Member

41 Posts

Posted - 2015-03-14 : 07:46:16

quote:
Originally posted by gbritton

CROSS JOIN

Thought cross join would show me multiples of TableA for every row for tableT.

I tested it and the results are odd
See below

SELECT ISNULL(A.Col1,B.Col1) as Col1,Col2,Col3 FROM(
SELECT 1 Col1,100 Col2 UNION SELECT 2 Col1,200 Col2) A
CROSS JOIN
(SELECT 1 Col1,300 Col3 UNION SELECT 3 Col1,400 Col3) B

gives me
1 100 300
1 100 400
2 200 300
2 200 400

Notice how the value 3 doesn't even appear in the output. Odd.

However I want
Col1 Col2 Col3
1 100 NULL
2 200 NULL
1 NULL 300
3 NULL 400

which is what full outer join on 1=2 gives me
SELECT ISNULL(A.Col1,B.Col1) as Col1,Col2,Col3 FROM(
SELECT 1 Col1,100 Col2 UNION SELECT 2 Col1,200 Col2) A
FULL OUTER JOIN
(SELECT 1 Col1,300 Col3 UNION SELECT 3 Col1,400 Col3) B
ON 1=2

madlo
Starting Member

41 Posts

Posted - 2015-03-14 : 08:17:51

Reading this
http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx
I'm not sure if I should go back to union as performance is going to be an issue as the use case is similar to a budget vs actuals with with a hudreds of thousands of lines and more columns.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-14 : 10:41:01

Your problem is what's in your outer select. e.g. If you just select * you'll get:


Col1	Col2	Col1	Col3
1	100	1	300
2	200	1	300
1	100	3	400
2	200	3	400

So, 3 is there, but your ISNULL expression is always selecting from A. Which makes sense since, by definition, a CROSS APPLY is a Cartesian product, so A.col1 will never be null.

I agree that the FULL JOIN on a False predicate is unattractive, but on the other hand I can't imagine what your desired results actually would mean. Is this just a toy example or is there a real business problem you are trying to solve?

madlo
Starting Member

41 Posts

Posted - 2015-03-17 : 05:18:42

It's a real business case.

See it as a budget vs actuals with or without committed in both local and foreign currency including some other columns.

Basically 5 unions and thus a lot of false predicates currently which I want to rewrite into a join so it looks neater.

The result of the query is passed to a reporting tool which sums it up per account and displays it in one row. The query could also do it, it would make no difference to the end result.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 09:51:34

OK -- this gives the desired result with the test data. Not sure about your production data though:


select isnull(a.Col1, b.col1), 
	   case when a.col1 = b.col1 and a.col2 = b.col2 then a.col2 
	        when a.col1 <> b.col1 then b.col2
	   end as col2,
	   case when a.col1 = b.col1 and a.col2 = b.col2 then null 
	        else b.col2
	   end as col2
from (SELECT 1 Col1,100 Col2 UNION SELECT 2 Col1,200 Col2) A
full join (
	SELECT 1 Col1,100 Col2 UNION SELECT 2 Col1,200 Col2
	union all
	SELECT 1 Col1,300 Col2 UNION SELECT 3 Col1,400 Col3
) b
 on a.col1 = b.col1
return

madlo
Starting Member

41 Posts

Posted - 2015-03-18 : 12:18:27

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources