Create a single query that repeats the rows of one table and multiplies fields according to another

[sheetspread]

Right now I can only do this by creating numerous queries, but one neat simple format like below would help tremendously. Here's the first table:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Type	January	February	March	April	May	June	July	August	September	October	November	December
2	Bennington	Orange	2	98	48	75	88	62	75	38	96	71	24	1
3	Carney	Green	67	57	10	12	77	47	73	21	70	46	33	0
4	Council Hill	Yellow	1	54	71	70	63	77	94	71	50	22	1	39
5	Fairmont	Orange	53	98	95	9	31	70	83	76	3	39	60	76
6	Foyil	Blue	72	55	45	11	20	0	2	75	45	64	29	92
7	Hallett	White	15	26	9	18	45	80	96	35	66	63	14	72
8	Headrick	Yellow	2	99	59	1	82	21	70	80	36	90	47	88
9	Indiahoma	Yellow	1	75	3	79	89	71	64	83	65	58	22	39
10	Lambert	White	38	82	53	4	20	86	61	47	91	66	80	34
11	Le Flore	Green	49	3	55	85	46	98	25	77	96	4	85	8
12	Maysville	White	72	95	92	35	61	60	36	98	30	63	56	4
13	Mulhall	Blue	78	24	4	71	92	72	8	73	15	48	70	40
14	Muskogee	Blue	84	63	95	16	15	5	32	93	94	9	44	52
15	Oak Grove	Yellow	36	30	9	97	51	95	85	94	47	12	76	29
16	Olustee	Orange	96	90	42	8	11	0	76	95	75	11	10	16
17	Quapaw	Blue	67	22	97	48	15	66	99	28	13	85	69	10
18	Stidham	Yellow	54	86	81	42	75	39	48	62	93	26	99	27
19	Terlton	Orange	5	99	17	42	2	87	82	94	59	62	79	71
20	Wanette	Green	19	33	75	8	33	84	96	14	24	7	4	90
DataTable

The allocation is in this table:

Excel Workbook
	A	B	C	D	E	F
1	Type	North	South	East	West	Central
2	Orange	0.41	0.21	0.38	0	0
3	Yellow	0.06	0	0.21	0.27	0.46
4	Green	0.14	0.36	0.22	0.15	0.13
5	Blue	0.16	0.33	0.05	0.4	0.06
6	White	0.31	0.13	0.16	0	0.4
PercentsTable

And this is exactly how I'd like the query to appear:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	SubType	January	February	March	April	May	June	July	August	September	October	November	December
2	Bennington	North	0.82	40.18	19.68	30.75	36.08	25.42	30.75	15.58	39.36	29.11	9.84	0.41
3	Bennington	South	0.42	20.58	10.08	15.75	18.48	13.02	15.75	7.98	20.16	14.91	5.04	0.21
4	Bennington	East	0.76	37.24	18.24	28.5	33.44	23.56	28.5	14.44	36.48	26.98	9.12	0.38
5	Carney	North	9.38	7.98	1.4	1.68	10.78	6.58	10.22	2.94	9.8	6.44	4.62	0
6	Carney	South	24.12	20.52	3.6	4.32	27.72	16.92	26.28	7.56	25.2	16.56	11.88	0
7	Carney	East	14.74	12.54	2.2	2.64	16.94	10.34	16.06	4.62	15.4	10.12	7.26	0
8	Carney	West	10.05	8.55	1.5	1.8	11.55	7.05	10.95	3.15	10.5	6.9	4.95	0
9	Carney	Central	8.71	7.41	1.3	1.56	10.01	6.11	9.49	2.73	9.1	5.98	4.29	0
10	Council Hill	North	0.06	3.24	4.26	4.2	3.78	4.62	5.64	4.26	3	1.32	0.06	2.34
11	Council Hill	East	0.21	11.34	14.91	14.7	13.23	16.17	19.74	14.91	10.5	4.62	0.21	8.19
12	Council Hill	West	0.27	14.58	19.17	18.9	17.01	20.79	25.38	19.17	13.5	5.94	0.27	10.53
13	Council Hill	Central	0.46	24.84	32.66	32.2	28.98	35.42	43.24	32.66	23	10.12	0.46	17.94
14	Fairmont	North	21.73	40.18	38.95	3.69	12.71	28.7	34.03	31.16	1.23	15.99	24.6	31.16
15	Fairmont	South	11.13	20.58	19.95	1.89	6.51	14.7	17.43	15.96	0.63	8.19	12.6	15.96
16	Fairmont	East	20.14	37.24	36.1	3.42	11.78	26.6	31.54	28.88	1.14	14.82	22.8	28.88
17	Foyil	North	11.52	8.8	7.2	1.76	3.2	0	0.32	12	7.2	10.24	4.64	14.72
18	Foyil	South	23.76	18.15	14.85	3.63	6.6	0	0.66	24.75	14.85	21.12	9.57	30.36
19	Foyil	East	3.6	2.75	2.25	0.55	1	0	0.1	3.75	2.25	3.2	1.45	4.6
20	Foyil	West	28.8	22	18	4.4	8	0	0.8	30	18	25.6	11.6	36.8
21	Foyil	Central	4.32	3.3	2.7	0.66	1.2	0	0.12	4.5	2.7	3.84	1.74	5.52
22	Hallett	North	4.65	8.06	2.79	5.58	13.95	24.8	29.76	10.85	20.46	19.53	4.34	22.32
23	Hallett	South	1.95	3.38	1.17	2.34	5.85	10.4	12.48	4.55	8.58	8.19	1.82	9.36
24	Hallett	East	2.4	4.16	1.44	2.88	7.2	12.8	15.36	5.6	10.56	10.08	2.24	11.52
25	Hallett	Central	6	10.4	3.6	7.2	18	32	38.4	14	26.4	25.2	5.6	28.8
26	Headrick	North	0.12	5.94	3.54	0.06	4.92	1.26	4.2	4.8	2.16	5.4	2.82	5.28
27	Headrick	East	0.42	20.79	12.39	0.21	17.22	4.41	14.7	16.8	7.56	18.9	9.87	18.48
28	Headrick	West	0.54	26.73	15.93	0.27	22.14	5.67	18.9	21.6	9.72	24.3	12.69	23.76
29	Headrick	Central	0.92	45.54	27.14	0.46	37.72	9.66	32.2	36.8	16.56	41.4	21.62	40.48
30	Indiahoma	North	0.06	4.5	0.18	4.74	5.34	4.26	3.84	4.98	3.9	3.48	1.32	2.34
31	Indiahoma	East	0.21	15.75	0.63	16.59	18.69	14.91	13.44	17.43	13.65	12.18	4.62	8.19
32	Indiahoma	West	0.27	20.25	0.81	21.33	24.03	19.17	17.28	22.41	17.55	15.66	5.94	10.53
33	Indiahoma	Central	0.46	34.5	1.38	36.34	40.94	32.66	29.44	38.18	29.9	26.68	10.12	17.94
34	Lambert	North	11.78	25.42	16.43	1.24	6.2	26.66	18.91	14.57	28.21	20.46	24.8	10.54
35	Lambert	South	4.94	10.66	6.89	0.52	2.6	11.18	7.93	6.11	11.83	8.58	10.4	4.42
SingleQuery

cont'd

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
36	Lambert	East	6.08	13.12	8.48	0.64	3.2	13.76	9.76	7.52	14.56	10.56	12.8	5.44
37	Lambert	Central	15.2	32.8	21.2	1.6	8	34.4	24.4	18.8	36.4	26.4	32	13.6
38	Le Flore	North	6.86	0.42	7.7	11.9	6.44	13.72	3.5	10.78	13.44	0.56	11.9	1.12
39	Le Flore	South	17.64	1.08	19.8	30.6	16.56	35.28	9	27.72	34.56	1.44	30.6	2.88
40	Le Flore	East	10.78	0.66	12.1	18.7	10.12	21.56	5.5	16.94	21.12	0.88	18.7	1.76
41	Le Flore	West	7.35	0.45	8.25	12.75	6.9	14.7	3.75	11.55	14.4	0.6	12.75	1.2
42	Le Flore	Central	6.37	0.39	7.15	11.05	5.98	12.74	3.25	10.01	12.48	0.52	11.05	1.04
43	Maysville	North	22.32	29.45	28.52	10.85	18.91	18.6	11.16	30.38	9.3	19.53	17.36	1.24
44	Maysville	South	9.36	12.35	11.96	4.55	7.93	7.8	4.68	12.74	3.9	8.19	7.28	0.52
45	Maysville	East	11.52	15.2	14.72	5.6	9.76	9.6	5.76	15.68	4.8	10.08	8.96	0.64
46	Maysville	Central	28.8	38	36.8	14	24.4	24	14.4	39.2	12	25.2	22.4	1.6
47	Mulhall	North	12.48	3.84	0.64	11.36	14.72	11.52	1.28	11.68	2.4	7.68	11.2	6.4
48	Mulhall	South	25.74	7.92	1.32	23.43	30.36	23.76	2.64	24.09	4.95	15.84	23.1	13.2
49	Mulhall	East	3.9	1.2	0.2	3.55	4.6	3.6	0.4	3.65	0.75	2.4	3.5	2
50	Mulhall	West	31.2	9.6	1.6	28.4	36.8	28.8	3.2	29.2	6	19.2	28	16
51	Mulhall	Central	4.68	1.44	0.24	4.26	5.52	4.32	0.48	4.38	0.9	2.88	4.2	2.4
52	Muskogee	North	13.44	10.08	15.2	2.56	2.4	0.8	5.12	14.88	15.04	1.44	7.04	8.32
53	Muskogee	South	27.72	20.79	31.35	5.28	4.95	1.65	10.56	30.69	31.02	2.97	14.52	17.16
54	Muskogee	East	4.2	3.15	4.75	0.8	0.75	0.25	1.6	4.65	4.7	0.45	2.2	2.6
55	Muskogee	West	33.6	25.2	38	6.4	6	2	12.8	37.2	37.6	3.6	17.6	20.8
56	Muskogee	Central	5.04	3.78	5.7	0.96	0.9	0.3	1.92	5.58	5.64	0.54	2.64	3.12
57	Oak Grove	North	2.16	1.8	0.54	5.82	3.06	5.7	5.1	5.64	2.82	0.72	4.56	1.74
58	Oak Grove	East	7.56	6.3	1.89	20.37	10.71	19.95	17.85	19.74	9.87	2.52	15.96	6.09
59	Oak Grove	West	9.72	8.1	2.43	26.19	13.77	25.65	22.95	25.38	12.69	3.24	20.52	7.83
60	Oak Grove	Central	16.56	13.8	4.14	44.62	23.46	43.7	39.1	43.24	21.62	5.52	34.96	13.34
61	Olustee	North	39.36	36.9	17.22	3.28	4.51	0	31.16	38.95	30.75	4.51	4.1	6.56
62	Olustee	South	20.16	18.9	8.82	1.68	2.31	0	15.96	19.95	15.75	2.31	2.1	3.36
63	Olustee	East	36.48	34.2	15.96	3.04	4.18	0	28.88	36.1	28.5	4.18	3.8	6.08
64	Quapaw	North	10.72	3.52	15.52	7.68	2.4	10.56	15.84	4.48	2.08	13.6	11.04	1.6
65	Quapaw	South	22.11	7.26	32.01	15.84	4.95	21.78	32.67	9.24	4.29	28.05	22.77	3.3
66	Quapaw	East	3.35	1.1	4.85	2.4	0.75	3.3	4.95	1.4	0.65	4.25	3.45	0.5
67	Quapaw	West	26.8	8.8	38.8	19.2	6	26.4	39.6	11.2	5.2	34	27.6	4
68	Quapaw	Central	4.02	1.32	5.82	2.88	0.9	3.96	5.94	1.68	0.78	5.1	4.14	0.6
69	Stidham	North	3.24	5.16	4.86	2.52	4.5	2.34	2.88	3.72	5.58	1.56	5.94	1.62
70	Stidham	East	11.34	18.06	17.01	8.82	15.75	8.19	10.08	13.02	19.53	5.46	20.79	5.67
SingleQuery

cont'd

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
71	Stidham	West	14.58	23.22	21.87	11.34	20.25	10.53	12.96	16.74	25.11	7.02	26.73	7.29
72	Stidham	Central	24.84	39.56	37.26	19.32	34.5	17.94	22.08	28.52	42.78	11.96	45.54	12.42
73	Terlton	North	2.05	40.59	6.97	17.22	0.82	35.67	33.62	38.54	24.19	25.42	32.39	29.11
74	Terlton	South	1.05	20.79	3.57	8.82	0.42	18.27	17.22	19.74	12.39	13.02	16.59	14.91
75	Terlton	East	1.9	37.62	6.46	15.96	0.76	33.06	31.16	35.72	22.42	23.56	30.02	26.98
76	Wanette	North	2.66	4.62	10.5	1.12	4.62	11.76	13.44	1.96	3.36	0.98	0.56	12.6
77	Wanette	South	6.84	11.88	27	2.88	11.88	30.24	34.56	5.04	8.64	2.52	1.44	32.4
78	Wanette	East	4.18	7.26	16.5	1.76	7.26	18.48	21.12	3.08	5.28	1.54	0.88	19.8
79	Wanette	West	2.85	4.95	11.25	1.2	4.95	12.6	14.4	2.1	3.6	1.05	0.6	13.5
80	Wanette	Central	2.47	4.29	9.75	1.04	4.29	10.92	12.48	1.82	3.12	0.91	0.52	11.7
SingleQuery

So each record in the original table is repeated as many times as its nonzero corresponding subtypes in the PercentsTable, with the monthly numbers multiplied by that percentage.

 

[Fazza]

Hi,

The data is poorly set up. This greatly complicates any work. The data should be set up differently.

The base tables are set up like reports - cross tab reports. Data should instead be stored in tables in normalised form. Then working with the data is easier.

So, the first table instead of "Name", "Type", "January", "February", etc

Could be "Name", "Type", "Month", "Value"

And the Percents table instead of "Type", "North", "South", etc

Could be "Type", "Location", "Percent"

Then the query could be something like

TRANSFORM SUM(D.Value * P.Percent)
SELECT DISTINCT D.Name, P.Location
FROM DataTable D, PercentsTable P
WHERE D.Type = P.Type AND P.Percent>0
GROUP BY D.Name, P.Location
PIVOT D.Month IN ('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August','September', 'October', 'November', 'December')

This might be a little rough. It is just how I wrote it (in Excel, btw).

The only reason for the last line listing all the months is to get the order. I think setting things up better in the source tables would avoid that. Normally the line would just be "PIVOT D.Month"

Same sort of idea with your custom sort on the Location field. Define it in a source table and everything will be simpler.

If one were to absolutely insist on maintaining the poor structure, then the normalisation of the data could be included in the single query. This would be a really bad way to work. Change the "FROM table" to whatever you need. Like a UNION query on each current field.

SELECT Name, Type, 'January' AS [Month], January AS [Value]
FROM datatable
UNION
SELECT Name, Type, 'February' AS [Month], February AS [Value]
FROM datatable
etc etc to December

Same sort of thing for the other table. To be clear, this would never be a recommended approach.

HTH

 

[sheetspread]

Ah Fazza, thanks for replying. Yes, ideally there shouldn't be month fields to start with but the input data are reports themselves, in that format. So is this how the SQL should look?

SELECT Name, Type, 'January' AS [Month], January AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'February' AS [Month], February AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'March' AS [Month], March AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'April' AS [Month], April AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'May' AS [Month], May AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'June' AS [Month], June AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'July' AS [Month], July AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'August' AS [Month], August AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'September' AS [Month], September AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'October' AS [Month], October AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'November' AS [Month], November AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'December' AS [Month], December AS [Value]

SELECT Name, Type, 'North' AS [SubType], North AS [Value]
FROM PercentsTable
UNION
SELECT Name, Type, 'South' AS [SubType], South AS [Value]
FROM PercentsTable
UNION
SELECT Name, Type, 'East' AS [SubType], East AS [Value]
FROM PercentsTable
UNION
SELECT Name, Type, 'West' AS [SubType], West AS [Value]
FROM PercentsTable
UNION
SELECT Name, Type, 'Central' AS [SubType], Central AS [Value]
FROM PercentsTable
UNION

This gives me a syntax error, incomplete query clause. Do I need one last bit or is the Transform.....Pivot you have written above (or some variation) needed as well?

 

[Fazza]

for one query, the whole query would be like I wrote above except the line

FROM DataTable D, PercentsTable P

would be replaced by

FROM (SELECT etc etc) D, (SELECT etc etc) P

The new bits need to be complete within themselves so you can test them separately to get them correct.

Syntax as I wrote previously. That is,

SELECT first one
FROM table

UNION
SELECT second one
FROM table

UNION
SELECT third one
FROM table

etc etc

cheers

 

[sheetspread]

I hope this is closer, but there's a "syntax error in from clause" pointing to (in bold):

TRANSFORM Sum(D.Value*P.Fraction) AS Expr1
SELECT D.Name, P.SubType AS Expr2
FROM  SELECT Name, Type, 'January' AS [Month], January AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'February' AS [Month], February AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'March' AS [Month], March AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'April' AS [Month], April AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'May' AS [Month], May AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'June' AS [Month], June AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'July' AS [Month], July AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'August' AS [Month], August AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'September' AS [Month], September AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'October' AS [Month], October AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'November' AS [Month], November AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'December' AS [Month], December AS [Value]
FROM DataTable]. AS D, [SELECT Type, 'North' AS [SubType], North AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'South' AS [SubType], South AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'East' AS [SubType], East AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'West' AS [SubType], West AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'Central' AS [SubType], Central AS [Fraction]
FROM PercentsTable]. AS P
WHERE (((D.Type)=[P].[Type]) AND (([P].[Fraction])>0))
GROUP BY D.Name, P.SubType
PIVOT D.Month In ('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August','September', 'October', 'November', 'December');

 

[sheetspread]

After further tinkering the syntax error is further down (a good sign?), in the join operation:

TRANSFORM Sum(D.Value*P.Fraction) AS Expr1
SELECT D.Name, P.Subtype AS Expr2
FROM (SELECT Name, Type, 'January' AS [Month], January AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'February' AS [Month], February AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'March' AS [Month], March AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'April' AS [Month], April AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'May' AS [Month], May AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'June' AS [Month], June AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'July' AS [Month], July AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'August' AS [Month], August AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'September' AS [Month], September AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'October' AS [Month], October AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'November' AS [Month], November AS [Value]
FROM DataTable
UNION
SELECT Name, Type, 'December' AS [Month], December AS [Value]
FROM DataTable]) AS D, ([SELECT Type, 'North' AS [SubType], North AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'South' AS [SubType], South AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'East' AS [SubType], East AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'West' AS [SubType], West AS [Fraction]
FROM PercentsTable
UNION
SELECT Type, 'Central' AS [SubType], Central AS [Fraction]
FROM PercentsTable]) AS P
WHERE (((D.Type)=[P].[Type]) AND (([P].[Fraction])>0))
GROUP BY D.Name, P.SubType
PIVOT D.Month In ('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August','September', 'October', 'November', 'December');

I did test the two segments like you said, and they seem to be ok apart.