Transpose multiple columns for differents tables in SQL

Question

I have the next three tables:

Table A

Client	Consumption1	Consumption2	...	Consumption28
4587	325442	67420	...	364855
1056	104687	35879	...	8764501
8977	485236	68743	...	477285

Table B

Client	Arrive1	Arrive2	...	Arrive28
4587	926842	474230	...	634875
1278	154327	956948	...	84868
8977	694826	2587474	...	87547

Table C

Client	Stock1	Stock2	...	Stock28
4587	282	423	...	875
1056	153	948	...	886
8977	694	874	...	854

I would want a table like this:

Client	Stock	Consumption	Arrive
4587	282	325442	926842
4587	423	67420	474230
4587	...	...	...
4587	875	364855	634875
8977	694	485236	694826
8977	874	68743	474230
8977	...	...	...
8977	854	477285	634875

I try the next code:

CREATE TABLE FINAL_TB AS (
                          SELECT CLIENT FROM TABLE_A );

ALTER TABLE FINAL_TB ADD STOCK FLOAT(20);

INSERT INTO FINAL_TB(STOCK) SELECT TABLE_C.STOCK1 FROM TABLE_C 
                            UNION ALL 
                            SELECT TABLE_C.STOCK2 FROM TABLE_C
                            ...
                            UNION ALL 
                            SELECT TABLE_C.STOCK28 FROM TABLE_C;                         

But i don't get the result I want and the code is so extensive. Any help?

Answer 1

I created some temp tables with random values to illustrate:

create table #A
(
    Client int,
    Consumption1 int,
    Consumption2 int,
    Consumption3 int,
    Consumption4 int,
    Consumption5 int,
    Consumption6 int,
    Consumption7 int,
    Consumption8 int,
    Consumption9 int,
    Consumption10 int,
    Consumption11 int,
    Consumption12 int,
    Consumption13 int,
    Consumption14 int,
    Consumption15 int,
    Consumption16 int,
    Consumption17 int,
    Consumption18 int,
    Consumption19 int,
    Consumption20 int,
    Consumption21 int,
    Consumption22 int,
    Consumption23 int,
    Consumption24 int,
    Consumption25 int,
    Consumption26 int,
    Consumption27 int,
    Consumption28 int
)

create table #B
(
    Client int,
    Arrive1 int,
    Arrive2 int,
    Arrive3 int,
    Arrive4 int,
    Arrive5 int,
    Arrive6 int,
    Arrive7 int,
    Arrive8 int,
    Arrive9 int,
    Arrive10 int,
    Arrive11 int,
    Arrive12 int,
    Arrive13 int,
    Arrive14 int,
    Arrive15 int,
    Arrive16 int,
    Arrive17 int,
    Arrive18 int,
    Arrive19 int,
    Arrive20 int,
    Arrive21 int,
    Arrive22 int,
    Arrive23 int,
    Arrive24 int,
    Arrive25 int,
    Arrive26 int,
    Arrive27 int,
    Arrive28 int
)

create table #C
(
    Client int,
    Stock1 int,
    Stock2 int,
    Stock3 int,
    Stock4 int,
    Stock5 int,
    Stock6 int,
    Stock7 int,
    Stock8 int,
    Stock9 int,
    Stock10 int,
    Stock11 int,
    Stock12 int,
    Stock13 int,
    Stock14 int,
    Stock15 int,
    Stock16 int,
    Stock17 int,
    Stock18 int,
    Stock19 int,
    Stock20 int,
    Stock21 int,
    Stock22 int,
    Stock23 int,
    Stock24 int,
    Stock25 int,
    Stock26 int,
    Stock27 int,
    Stock28 int
)

insert into #A values
(4587, 1897163, 1750380, 5487230, 5086290, 3232280, 6014338, 5978823, 5864160, 8462496, 3945424, 6641780, 6268308, 924802, 9246014, 8089559, 6079526, 7332457, 4971714, 379325, 1651062, 5878994, 4987033, 9802665, 8163241, 180040, 3309288, 6966085, 6791869),
(1056, 3693126, 6085435, 2576225, 906414, 9308827, 7912118, 4690652, 2298023, 5333767, 1550576, 3324191, 8750357, 2135258, 3403408, 6807101, 1750717, 8367234, 5176016, 1008922, 9641689, 3564379, 5398274, 4521049, 5767916, 779819, 5466675, 4020480, 7245496),
(8977, 6496397, 7097447, 5026248, 3686617, 4583368, 4018366, 5718120, 318667, 2281474, 4044423, 9424092, 5355506, 7723824, 8635663, 7122301, 3401243, 2478769, 6540496, 8370652, 268827, 8858460, 4468893, 2184993, 416313, 5099858, 170593, 6609073, 7137023)

insert into #B values
(4587, 2667122, 2542268, 1118532, 2284504, 2147917, 1084679, 2729616, 1175793, 2649496, 1699428, 507390, 108714, 828544, 1358961, 1754942, 2084233, 1964319, 1751282, 2047706, 705322, 2918377, 1001676, 1494300, 2937679, 2216776, 2485884, 1217744, 366044),
(1056, 213746, 483120, 1689874, 2063834, 473576, 2063958, 2212633, 819822, 1569991, 1159024, 479248, 495937, 1923624, 1557142, 1164307, 2669944, 2161610, 1187291, 1083194, 1947235, 136211, 1749596, 692847, 1947817, 207300, 2784799, 2061996, 1248644),
(8977, 2085112, 2935995, 1977982, 633723, 2426369, 1156016, 1011041, 470338, 926361, 1244144, 625813, 945190, 1217648, 376607, 89393, 2944627, 1118893, 2939117, 213832, 2269515, 2261478, 1637021, 1961149, 1145781, 206511, 1563378, 1272576, 212847)

insert into #C values
(4587, 806, 797, 310, 784, 411, 629, 966, 134, 963, 313, 160, 136, 620, 690, 945, 649, 718, 207, 457, 125, 311, 911, 799, 629, 404, 510, 650, 793),
(1056, 599, 411, 843, 311, 752, 560, 797, 304, 805, 289, 476, 535, 315, 336, 805, 487, 820, 125, 589, 269, 415, 409, 329, 245, 316, 737, 561, 176),
(8977, 841, 583, 833, 372, 374, 776, 152, 676, 854, 404, 517, 571, 694, 315, 315, 116, 163, 948, 598, 681, 226, 691, 684, 372, 450, 772, 646, 581)

If you know that every possible Client value will exist in the Consumption table for example, you can just sum the fields and join the tables:

select 
    con.Client,
    sto.Stock1 + sto.Stock2 + sto.Stock3 + sto.Stock4 + sto.Stock5 + sto.Stock6 + sto.Stock7 + sto.Stock8 + sto.Stock9 + sto.Stock10 + sto.Stock11 + sto.Stock12 + sto.Stock13 + sto.Stock14 + sto.Stock15 + sto.Stock16 + sto.Stock17 + sto.Stock18 + sto.Stock19 + sto.Stock20 + sto.Stock21 + sto.Stock22 + sto.Stock23 + sto.Stock24 + sto.Stock25 + sto.Stock26 + sto.Stock27 + sto.Stock28 as Stock,
    con.Consumption1 + con.Consumption2 + con.Consumption3 + con.Consumption4 + con.Consumption5 + con.Consumption6 + con.Consumption7 + con.Consumption8 + con.Consumption9 + con.Consumption10 + con.Consumption11 + con.Consumption12 + con.Consumption13 + con.Consumption14 + con.Consumption15 + con.Consumption16 + con.Consumption17 + con.Consumption18 + con.Consumption19 + con.Consumption20 + con.Consumption21 + con.Consumption22 + con.Consumption23 + con.Consumption24 + con.Consumption25 + con.Consumption26 + con.Consumption27 + con.Consumption28 as Consumption,
    arr.Arrive1 + arr.Arrive2 + arr.Arrive3 + arr.Arrive4 + arr.Arrive5 + arr.Arrive6 + arr.Arrive7 + arr.Arrive8 + arr.Arrive9 + arr.Arrive10 + arr.Arrive11 + arr.Arrive12 + arr.Arrive13 + arr.Arrive14 + arr.Arrive15 + arr.Arrive16 + arr.Arrive17 + arr.Arrive18 + arr.Arrive19 + arr.Arrive20 + arr.Arrive21 + arr.Arrive22 + arr.Arrive23 + arr.Arrive24 + arr.Arrive25 + arr.Arrive26 + arr.Arrive27 + arr.Arrive28 as Arrive
from #A con
left join #B arr on con.Client = arr.Client
left join #C sto on con.Client = sto.Client

If it's possible that a Client might exist in only one or two of the tables and you can't know which one(s), then you need to distinct list of clients first:

select
    cli.Client,
    sto.Stock1 + sto.Stock2 + sto.Stock3 + sto.Stock4 + sto.Stock5 + sto.Stock6 + sto.Stock7 + sto.Stock8 + sto.Stock9 + sto.Stock10 + sto.Stock11 + sto.Stock12 + sto.Stock13 + sto.Stock14 + sto.Stock15 + sto.Stock16 + sto.Stock17 + sto.Stock18 + sto.Stock19 + sto.Stock20 + sto.Stock21 + sto.Stock22 + sto.Stock23 + sto.Stock24 + sto.Stock25 + sto.Stock26 + sto.Stock27 + sto.Stock28 as Stock,
    con.Consumption1 + con.Consumption2 + con.Consumption3 + con.Consumption4 + con.Consumption5 + con.Consumption6 + con.Consumption7 + con.Consumption8 + con.Consumption9 + con.Consumption10 + con.Consumption11 + con.Consumption12 + con.Consumption13 + con.Consumption14 + con.Consumption15 + con.Consumption16 + con.Consumption17 + con.Consumption18 + con.Consumption19 + con.Consumption20 + con.Consumption21 + con.Consumption22 + con.Consumption23 + con.Consumption24 + con.Consumption25 + con.Consumption26 + con.Consumption27 + con.Consumption28 as Consumption,
    arr.Arrive1 + arr.Arrive2 + arr.Arrive3 + arr.Arrive4 + arr.Arrive5 + arr.Arrive6 + arr.Arrive7 + arr.Arrive8 + arr.Arrive9 + arr.Arrive10 + arr.Arrive11 + arr.Arrive12 + arr.Arrive13 + arr.Arrive14 + arr.Arrive15 + arr.Arrive16 + arr.Arrive17 + arr.Arrive18 + arr.Arrive19 + arr.Arrive20 + arr.Arrive21 + arr.Arrive22 + arr.Arrive23 + arr.Arrive24 + arr.Arrive25 + arr.Arrive26 + arr.Arrive27 + arr.Arrive28 as Arrive
from
(
    select con.Client from #A con
    union
    select arr.Client from #B arr
    union
    select sto.Client from #C sto
) cli
left join #A con on cli.Client = con.Client
left join #B arr on cli.Client = arr.Client
left join #C sto on cli.Client = sto.Client

However, as has been pointed out in the comments, this table design seems pretty bad and should probably be changed, for example:

Client	ValueNumber	Consumption
4587	1	1897163
4587	2	1750380
4587	3	5487230
4587	4	5086290
4587	5	3232280
4587	6	6014338
4587	7	5978823
4587	8	5864160
4587	9	8462496
4587	10	3945424
4587	11	6641780
4587	12	6268308
4587	13	924802
4587	14	9246014
4587	15	8089559
4587	16	6079526
4587	17	7332457
4587	18	4971714
4587	19	379325
4587	20	1651062
4587	21	5878994
4587	22	4987033
4587	23	9802665
4587	24	8163241
4587	25	180040
4587	26	3309288
4587	27	6966085
4587	28	6791869
1056	1	3693126
1056	2	6085435
1056	3	2576225
1056	4	906414
1056	5	9308827
1056	6	7912118
1056	7	4690652
1056	8	2298023
1056	9	5333767
1056	10	1550576
1056	11	3324191
1056	12	8750357
1056	13	2135258
1056	14	3403408
1056	15	6807101
1056	16	1750717
1056	17	8367234
1056	18	5176016
1056	19	1008922
1056	20	9641689
1056	21	3564379
1056	22	5398274
1056	23	4521049
1056	24	5767916
1056	25	779819
1056	26	5466675
1056	27	4020480
1056	28	7245496
8977	1	6496397
8977	2	7097447
8977	3	5026248
8977	4	3686617
8977	5	4583368
8977	6	4018366
8977	7	5718120
8977	8	318667
8977	9	2281474
8977	10	4044423
8977	11	9424092
8977	12	5355506
8977	13	7723824
8977	14	8635663
8977	15	7122301
8977	16	3401243
8977	17	2478769
8977	18	6540496
8977	19	8370652
8977	20	268827
8977	21	8858460
8977	22	4468893
8977	23	2184993
8977	24	416313
8977	25	5099858
8977	26	170593
8977	27	6609073
8977	28	7137023

Or even:

Client	ValueNumber	Stock	Consumption	Arrive
4587	1	806	1897163	2667122
4587	2	797	1750380	2542268
4587	3	310	5487230	1118532
4587	4	784	5086290	2284504
4587	5	411	3232280	2147917
4587	6	629	6014338	1084679
4587	7	966	5978823	2729616
4587	8	134	5864160	1175793
4587	9	963	8462496	2649496
4587	10	313	3945424	1699428
4587	11	160	6641780	507390
4587	12	136	6268308	108714
4587	13	620	924802	828544
4587	14	690	9246014	1358961
4587	15	945	8089559	1754942
4587	16	649	6079526	2084233
4587	17	718	7332457	1964319
4587	18	207	4971714	1751282
4587	19	457	379325	2047706
4587	20	125	1651062	705322
4587	21	311	5878994	2918377
4587	22	911	4987033	1001676
4587	23	799	9802665	1494300
4587	24	629	8163241	2937679
4587	25	404	180040	2216776
4587	26	510	3309288	2485884
4587	27	650	6966085	1217744
4587	28	793	6791869	366044
1056	1	599	3693126	213746
1056	2	411	6085435	483120
1056	3	843	2576225	1689874
1056	4	311	906414	2063834
1056	5	752	9308827	473576
1056	6	560	7912118	2063958
1056	7	797	4690652	2212633
1056	8	304	2298023	819822
1056	9	805	5333767	1569991
1056	10	289	1550576	1159024
1056	11	476	3324191	479248
1056	12	535	8750357	495937
1056	13	315	2135258	1923624
1056	14	336	3403408	1557142
1056	15	805	6807101	1164307
1056	16	487	1750717	2669944
1056	17	820	8367234	2161610
1056	18	125	5176016	1187291
1056	19	589	1008922	1083194
1056	20	269	9641689	1947235
1056	21	415	3564379	136211
1056	22	409	5398274	1749596
1056	23	329	4521049	692847
1056	24	245	5767916	1947817
1056	25	316	779819	207300
1056	26	737	5466675	2784799
1056	27	561	4020480	2061996
1056	28	176	7245496	1248644
8977	1	841	6496397	2085112
8977	2	583	7097447	2935995
8977	3	833	5026248	1977982
8977	4	372	3686617	633723
8977	5	374	4583368	2426369
8977	6	776	4018366	1156016
8977	7	152	5718120	1011041
8977	8	676	318667	470338
8977	9	854	2281474	926361
8977	10	404	4044423	1244144
8977	11	517	9424092	625813
8977	12	571	5355506	945190
8977	13	694	7723824	1217648
8977	14	315	8635663	376607
8977	15	315	7122301	89393
8977	16	116	3401243	2944627
8977	17	163	2478769	1118893
8977	18	948	6540496	2939117
8977	19	598	8370652	213832
8977	20	681	268827	2269515
8977	21	226	8858460	2261478
8977	22	691	4468893	1637021
8977	23	684	2184993	1961149
8977	24	372	416313	1145781
8977	25	450	5099858	206511
8977	26	772	170593	1563378
8977	27	646	6609073	1272576
8977	28	581	7137023	212847

In which case your query becomes much simpler:

select
    Client,
    sum(Stock) as Stock,
    sum(Consumption) as Consumption,
    sum(Arrive) as Arrive
from combined_table
group by Client