SQL_DEV/EDUCATION/cross_join.sql at master · Brezencat/SQL_DEV · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
--Example used cross join

--==============================================================================
--it's source data
--==============================================================================

SELECT *
FROM (
		values('client-1', 10, 'client-777')
) AS carry([OUT], AMMOUNT, [IN]);

--OUT		AMMOUNT	IN
--client-1	10		client-777

--==============================================================================
--adding cross join
--==============================================================================

SELECT *
FROM (
		values('client-1', 10, 'client-777')
) AS carry([OUT], AMMOUNT, [IN])
CROSS JOIN (
			select 1
			union all
			select -1
) AS n(c);

--OUT		AMMOUNT	IN			c
--client-1	10		client-777	1
--client-1	10		client-777	-1

--==============================================================================
--total result
--==============================================================================

SELECT	CASE n.c
			WHEN -1 THEN [OUT]
			WHEN 1 THEN [IN]
			ELSE NULL
		END AS [OUT]
	, AMMOUNT * n.c AS AMMOUNT
	, CASE n.c
			WHEN 1 THEN [OUT]
			WHEN -1 THEN [IN]
			ELSE NULL
		END AS [IN]
FROM (
		values('client-1', 10, 'client-777')
) AS carry([OUT], AMMOUNT, [IN])
CROSS JOIN (
			select 1
			union all
			select -1
) AS n(c);

--OUT			AMMOUNT		IN
--client-777	10			client-1
--client-1		-10			client-777

--==============================================================================