扩展sql查询推荐

Question

我有一个像这样的表

a1      a2       a3      a4     a5   a6       a7     a8     a9       a10     a11...a25 
---------------------------------------------------------------------------------------
1.637   2.445   2.64    2.646   5   3.242   3.227   2.767   2.755   2.755   2.99 .. 0.78     

0.864   4.581   0.788   0.768   3   0.77    0.824   0.927   0.891   0.788   0.909   0.913..     
1.634   2.442   2.641   2.643   5   3.24    3.228   2.769   2.759   2.755   2.931.. 0.89    
    ... LOTS OF ROWS ....
1.763   3.842   0.925   0.768   2   0.757   0.765   0.411   0.886   0.791   0.783   0.898..     

要得到这样的表：

ID       Name           value
-------------------------------------
1   SQRT(SUM(a1*a1))    11.521301
2   SQRT(SUM(a2*a2))    492.271946
3   SQRT(SUM(a3*a3))    498.4920730002
4   SQRT(SUM(a4*a4))    520.5063090001
5   SQRT(SUM(a5*a5))    540.2042299997
6   SQRT(SUM(a6*a6))    539.4859119999
7   SQRT(SUM(a7*a7))    772.6103970002
8   SQRT(SUM(a8*a8))    450.866498
9   SQRT(SUM(a9*a9))    647.0218509999
10  SQRT(SUM(a10*a10))  500.7941099997
11  SQRT(SUM(a11*a11))  479.9509640001
12  SQRT(SUM(a12*a12))  368.3059439999
13  SQRT(SUM(a13*a13))  356.8316499999
14  SQRT(SUM(a14*a14))  343.143113
15  SQRT(SUM(a15*a15))  498.3066450001
16  SQRT(SUM(a16*a16))  167.688123
17  SQRT(SUM(a17*a17))  186.94422
18  SQRT(SUM(a18*a18))  87.13581799998
19  SQRT(SUM(a19*a19))  155.545215
20  SQRT(SUM(a20*a20))  128.5472860001
21  SQRT(SUM(a21*a21))  97.211263
22  SQRT(SUM(a22*a22))  323.54238
23  SQRT(SUM(a23*a23))  291.559957
24  SQRT(SUM(a24*a24))  214.907172
25  SQRT(SUM(a25*a25))  307 

我正在做：

 INSERT #temp (ID,NAME,Value) (
              SELECT 1, 'SQRT(SUM(a1*a1))', SQRT(SUM(a1*a1)) FROM myTable
    UNION ALL SELECT 2, 'SQRT(SUMa2*a2)) ', SQRT(SUMa2*a2)) FROM myTable
    UNION ALL SELECT 3, 'SQRT(SUMa3*a3)) ', SQRT(SUMa3*a3)) FROM myTable
    UNION ALL SELECT 4, 'SQRT(SUMa4*a4)) ', SQRT(SUMa4*a4)) FROM myTable
    UNION ALL SELECT 5, 'SQRT(SUMa5*a5)) ', SQRT(SUMa5*a5)) FROM myTable
    UNION ALL SELECT 6, 'SQRT(SUMa6*a6)) ', SQRT(SUMa6*a6)) FROM myTable
    UNION ALL SELECT 7, 'SQRT(SUMa7*a7)) ', SQRT(SUMa7*a7)) FROM myTable
    UNION ALL SELECT 8, 'SQRT(SUMa8*a8)) ', SQRT(SUMa8*a8)) FROM myTable
    UNION ALL SELECT 9, 'SQRT(SUMa9*a9)) ', SQRT(SUMa9*a9)) FROM myTable
    UNION ALL SELECT 10, 'SQRT(SUMa10*a10)) ', SQRT(SUMa10*a10)) FROM myTable
    UNION ALL SELECT 11, 'SQRT(SUMa11*a11)) ', SQRT(SUMa11*a11)) FROM myTable
    UNION ALL SELECT 12, 'SQRT(SUMa12*a12)) ', SQRT(SUMa12*a12)) FROM myTable
    UNION ALL SELECT 13, 'SQRT(SUMa13*a13)) ', SQRT(SUMa13*a13)) FROM myTable
    UNION ALL SELECT 14, 'SQRT(SUMa14*a14)) ', SQRT(SUMa14*a14)) FROM myTable
    UNION ALL SELECT 15, 'SQRT(SUMa15*a15)) ', SQRT(SUMa15*a15)) FROM myTable
    UNION ALL SELECT 16, 'SQRT(SUMa16*a16)) ', SQRT(SUMa16*a16)) FROM myTable
    UNION ALL SELECT 17, 'SQRT(SUMa17*a17)) ', SQRT(SUMa17*a17)) FROM myTable
    UNION ALL SELECT 18, 'SQRT(SUMa18*a18)) ', SQRT(SUMa18*a18)) FROM myTable
    UNION ALL SELECT 19, 'SQRT(SUMa19*a19)) ', SQRT(SUMa19*a19)) FROM myTable
    UNION ALL SELECT 20, 'SQRT(SUMa20*a20)) ', SQRT(SUMa20*a20)) FROM myTable
    UNION ALL SELECT 21, 'SQRT(SUMa21*a21)) ', SQRT(SUMa21*a21)) FROM myTable
    UNION ALL SELECT 22, 'SQRT(SUMa22*a22)) ', SQRT(SUMa22*a22)) FROM myTable
    UNION ALL SELECT 23, 'SQRT(SUMa23*a23)) ', SQRT(SUMa23*a23)) FROM myTable
    UNION ALL SELECT 24, 'SQRT(SUMa24*a24)) ', SQRT(SUMa24*a24)) FROM myTable
    UNION ALL SELECT 25, 'SQRT(SUMa25*a25)) ', SQRT(SUMa25*a25)) FROM myTable
    )

我如何修改查询，使其动态可能有很多列（超过25..）并得到类似的结果每一列逐列：

 ID      Name       value
 ---------------------------
1   SQRT(SUM(a1*a1))    15331.521301
2   SQRT(SUM(a1*a2))    20092.046062
3   SQRT(SUM(a1*a3))    20755.8496929999
4   SQRT(SUM(a1*a4))    22786.076923
5   SQRT(SUM(a1*a5))    23871.633871
6   SQRT(SUM(a1*a6))    22589.272653
7   SQRT(SUM(a1*a7))    25147.201917
8   SQRT(SUM(a1*a8))    19452.9791880001
9   SQRT(SUM(a1*a9))    23282.113233
10  SQRT(SUM(a1*a10))   21141.6634169999
11  SQRT(SUM(a1*a11))   19213.6884449999
12  SQRT(SUM(a1*a12))   15395.8691000001
13  SQRT(SUM(a1*a13))   16100.0673840001
14  SQRT(SUM(a1*a14))   16061.7523660001
15  SQRT(SUM(a1*a15))   16691.3522300001
16  SQRT(SUM(a1*a16))   9968.59303899999
17  SQRT(SUM(a1*a17))   8515.01189500001
18  SQRT(SUM(a1*a18))   7944.39528999999
19  SQRT(SUM(a1*a19))   9424.95011500001
20  SQRT(SUM(a1*a20))   9549.36467900003
21  SQRT(SUM(a1*a21))   9247.69508900001
22  SQRT(SUM(a1*a22))   16627.328868
23  SQRT(SUM(a1*a23))   14649.86024
24  SQRT(SUM(a1*a24))   15034.150613
25  SQRT(SUM(a1*a25))   19330.063
26  SQRT(SUM(a2*a2))    40392.271946
27  SQRT(SUM(a2*a3))    34705.4652099999
28  SQRT(SUM(a2*a4))    37152.0190690001
29  SQRT(SUM(a2*a5))    39102.0158719999
30  SQRT(SUM(a2*a6))    37210.5962529999
31  SQRT(SUM(a2*a7))    41948.4831799999
32  SQRT(SUM(a2*a8))    32180.254414
33  SQRT(SUM(a2*a9))    39136.205808
34  SQRT(SUM(a2*a10))   35031.0716169998
35  SQRT(SUM(a2*a11))   30031.417876
36  SQRT(SUM(a2*a12))   24967.433735
37  SQRT(SUM(a2*a13))   24757.487372
38  SQRT(SUM(a2*a14))   26372.26347
39  SQRT(SUM(a2*a15))   27193.5868959999
40  SQRT(SUM(a2*a16))   14995.577807
41  SQRT(SUM(a2*a17))   12099.770828
42  SQRT(SUM(a2*a18))   11331.541292
43  SQRT(SUM(a2*a19))   13689.111348
44  SQRT(SUM(a2*a20))   14304.304684
45  SQRT(SUM(a2*a21))   13262.453616
46  SQRT(SUM(a2*a22))   22555.0190409999
47  SQRT(SUM(a2*a23))   20317.761134
48  SQRT(SUM(a2*a24))   20970.8418399999
49  SQRT(SUM(a2*a25))   30111.567
50  SQRT(SUM(a3*a3))    42298.4920730002
51  SQRT(SUM(a3*a4))    41515.9032480001
52  SQRT(SUM(a3*a5))    43187.8289579999
53  SQRT(SUM(a3*a6))    40698.5131719999
54  SQRT(SUM(a3*a7))    44812.753709
55  SQRT(SUM(a3*a8))    33092.9596450001
56  SQRT(SUM(a3*a9))    40419.9384819999
57  SQRT(SUM(a3*a10))   36875.0149760001
58  SQRT(SUM(a3*a11))   33146.958322
59  SQRT(SUM(a3*a12))   26222.052961
60  SQRT(SUM(a3*a13))   27125.494473
61  SQRT(SUM(a3*a14))   28010.209528
62  SQRT(SUM(a3*a15))   28401.8073419999
63  SQRT(SUM(a3*a16))   16178.941329
64  SQRT(SUM(a3*a17))   13000.048318
65  SQRT(SUM(a3*a18))   11739.448438
66  SQRT(SUM(a3*a19))   13875.4740580001
67  SQRT(SUM(a3*a20))   14567.603957
68  SQRT(SUM(a3*a21))   13736.9749419999
69  SQRT(SUM(a3*a22))   25541.990367
70  SQRT(SUM(a3*a23))   22107.401613
71  SQRT(SUM(a3*a24))   22034.6964049999
72  SQRT(SUM(a3*a25))   31431.191
73  SQRT(SUM(a4*a4))    52020.5063090001
74  SQRT(SUM(a4*a5))    51136.4496159998
75  SQRT(SUM(a4*a6))    47030.194553
76  SQRT(SUM(a4*a7))    50444.0546410001
77  SQRT(SUM(a4*a8))    37744.658766
78  SQRT(SUM(a4*a9))    46047.6280759999
79  SQRT(SUM(a4*a10))   42136.311509
80  SQRT(SUM(a4*a11))   37745.2996749999
81  SQRT(SUM(a4*a12))   30222.3225650001
82  SQRT(SUM(a4*a13))   30969.743114
83  SQRT(SUM(a4*a14))   29950.966638
84  SQRT(SUM(a4*a15))   30328.3451680001
85  SQRT(SUM(a4*a16))   18514.180341
86  SQRT(SUM(a4*a17))   14289.516416
87  SQRT(SUM(a4*a18))   12737.467982
88  SQRT(SUM(a4*a19))   14594.445791
89  SQRT(SUM(a4*a20))   15654.928199
90  SQRT(SUM(a4*a21))   15036.68767
91  SQRT(SUM(a4*a22))   30159.960792
92  SQRT(SUM(a4*a23))   25220.3214460001
93  SQRT(SUM(a4*a24))   24741.6341310001
94  SQRT(SUM(a4*a25))   35381.978
95  SQRT(SUM(a5*a5))    57540.2042299997
96  SQRT(SUM(a5*a6))    51290.1543070001
97  SQRT(SUM(a5*a7))    54896.8684820001
98  SQRT(SUM(a5*a8))    40986.019717
99  SQRT(SUM(a5*a9))    48764.360706
100 SQRT(SUM(a5*a10))   44762.377804
101 SQRT(SUM(a5*a11))   39901.3598239999
102 SQRT(SUM(a5*a12))   32584.48135
103 SQRT(SUM(a5*a13))   33377.848045
104 SQRT(SUM(a5*a14))   32124.4827750001
105 SQRT(SUM(a5*a15))   31555.597109
106 SQRT(SUM(a5*a16))   18989.026965
107 SQRT(SUM(a5*a17))   14868.379199
108 SQRT(SUM(a5*a18))   13249.428191
109 SQRT(SUM(a5*a19))   15002.680093
110 SQRT(SUM(a5*a20))   15742.096991
111 SQRT(SUM(a5*a21))   15405.7245760001
112 SQRT(SUM(a5*a22))   30017.011661
113 SQRT(SUM(a5*a23))   26189.667716
114 SQRT(SUM(a5*a24))   26215.478825
115 SQRT(SUM(a5*a25))   37003.539
116 SQRT(SUM(a6*a6))    54639.4859119999
117 SQRT(SUM(a6*a7))    56681.9804190001
118 SQRT(SUM(a6*a8))    41658.3506059999
119 SQRT(SUM(a6*a9))    50425.6270699999
120 SQRT(SUM(a6*a10))   44533.9831760001
121 SQRT(SUM(a6*a11))   38811.0935879999
122 SQRT(SUM(a6*a12))   31365.8610789999
123 SQRT(SUM(a6*a13))   32199.3686429999
124 SQRT(SUM(a6*a14))   32883.3662839999
125 SQRT(SUM(a6*a15))   32577.2608019999
126 SQRT(SUM(a6*a16))   17842.416946
127 SQRT(SUM(a6*a17))   14218.367288
128 SQRT(SUM(a6*a18))   12811.4411550001
129 SQRT(SUM(a6*a19))   14690.803277
130 SQRT(SUM(a6*a20))   15167.653396
131 SQRT(SUM(a6*a21))   14698.77157
132 SQRT(SUM(a6*a22))   26925.5275609999
133 SQRT(SUM(a6*a23))   23660.53667
134 SQRT(SUM(a6*a24))   24257.614885
135 SQRT(SUM(a6*a25))   36191.456
136 SQRT(SUM(a7*a7))    72172.6103970002
137 SQRT(SUM(a7*a8))    49935.6339020003
138 SQRT(SUM(a7*a9))    61668.680008
139 SQRT(SUM(a7*a10))   54374.0948010001
140 SQRT(SUM(a7*a11))   47660.6881530002
141 SQRT(SUM(a7*a12))   38288.774891
142 SQRT(SUM(a7*a13))   39136.7022
143 SQRT(SUM(a7*a14))   41133.3921509998
144 SQRT(SUM(a7*a15))   41559.9263100001
145 SQRT(SUM(a7*a16))   20769.124993
146 SQRT(SUM(a7*a17))   16637.359467
147 SQRT(SUM(a7*a18))   15127.20195
148 SQRT(SUM(a7*a19))   16942.3710710001
149 SQRT(SUM(a7*a20))   17565.583652
150 SQRT(SUM(a7*a21))   16704.877563
151 SQRT(SUM(a7*a22))   27996.123247
152 SQRT(SUM(a7*a23))   26089.616466
153 SQRT(SUM(a7*a24))   26557.416971
154 SQRT(SUM(a7*a25))   42671.2939999999
155 SQRT(SUM(a8*a8))    41950.866498
156 SQRT(SUM(a8*a9))    47739.2158789999
157 SQRT(SUM(a8*a10))   43630.6534010001
158 SQRT(SUM(a8*a11))   37787.976809
159 SQRT(SUM(a8*a12))   28824.60303
160 SQRT(SUM(a8*a13))   29370.272564
161 SQRT(SUM(a8*a14))   30152.628763
162 SQRT(SUM(a8*a15))   29906.538039
163 SQRT(SUM(a8*a16))   15833.940047
164 SQRT(SUM(a8*a17))   12613.687447
165 SQRT(SUM(a8*a18))   11485.172743
166 SQRT(SUM(a8*a19))   12823.868894
167 SQRT(SUM(a8*a20))   13212.669251
168 SQRT(SUM(a8*a21))   12583.873227
169 SQRT(SUM(a8*a22))   22176.8841029999
170 SQRT(SUM(a8*a23))   20209.618067
171 SQRT(SUM(a8*a24))   20879.543022
172 SQRT(SUM(a8*a25))   32204.2769999999
173 SQRT(SUM(a9*a9))    63347.0218509999
174 SQRT(SUM(a9*a10))   53762.972001
175 SQRT(SUM(a9*a11))   46157.9263400001
176 SQRT(SUM(a9*a12))   35317.621406
177 SQRT(SUM(a9*a13))   36447.227683
178 SQRT(SUM(a9*a14))   37731.5151540001
179 SQRT(SUM(a9*a15))   39236.1157149999
180 SQRT(SUM(a9*a16))   19540.641266
181 SQRT(SUM(a9*a17))   15258.807672
182 SQRT(SUM(a9*a18))   13782.8565210001
183 SQRT(SUM(a9*a19))   15761.362407
184 SQRT(SUM(a9*a20))   17036.273159
185 SQRT(SUM(a9*a21))   16081.397696
186 SQRT(SUM(a9*a22))   27165.8435370001
187 SQRT(SUM(a9*a23))   23906.0342330001
188 SQRT(SUM(a9*a24))   24066.8406580001
189 SQRT(SUM(a9*a25))   40316.1389999999
190 SQRT(SUM(a10*a10))  53100.7941099997
191 SQRT(SUM(a10*a11))  44028.611452
192 SQRT(SUM(a10*a12))  33254.968959
193 SQRT(SUM(a10*a13))  33507.173626
194 SQRT(SUM(a10*a14))  33831.1896060001
195 SQRT(SUM(a10*a15))  34566.2711740001
196 SQRT(SUM(a10*a16))  18335.753729
197 SQRT(SUM(a10*a17))  13956.658088
198 SQRT(SUM(a10*a18))  12544.134349
199 SQRT(SUM(a10*a19))  13924.429223
200 SQRT(SUM(a10*a20))  14805.586204
201 SQRT(SUM(a10*a21))  14364.397799
202 SQRT(SUM(a10*a22))  25466.314763
203 SQRT(SUM(a10*a23))  22108.4700470001
204 SQRT(SUM(a10*a24))  22231.302679
205 SQRT(SUM(a10*a25))  35920.821
206 SQRT(SUM(a11*a11))  45879.9509640001
207 SQRT(SUM(a11*a12))  31932.113237
208 SQRT(SUM(a11*a13))  32289.4195010001
209 SQRT(SUM(a11*a14))  31145.9640480001
210 SQRT(SUM(a11*a15))  30333.186591
211 SQRT(SUM(a11*a16))  16684.692641
212 SQRT(SUM(a11*a17))  13325.097796
213 SQRT(SUM(a11*a18))  11929.636027
214 SQRT(SUM(a11*a19))  13214.80155
215 SQRT(SUM(a11*a20))  12716.074835
216 SQRT(SUM(a11*a21))  12105.432398
217 SQRT(SUM(a11*a22))  23730.6834060001
218 SQRT(SUM(a11*a23))  20509.631936
219 SQRT(SUM(a11*a24))  20497.964743
220 SQRT(SUM(a11*a25))  32540.604
221 SQRT(SUM(a12*a12))  32068.3059439999
222 SQRT(SUM(a12*a13))  28603.5022109999
223 SQRT(SUM(a12*a14))  28636.4854419999
224 SQRT(SUM(a12*a15))  26761.630249
225 SQRT(SUM(a12*a16))  13263.409324
226 SQRT(SUM(a12*a17))  11345.883388
227 SQRT(SUM(a12*a18))  10519.122964
228 SQRT(SUM(a12*a19))  12026.090769
229 SQRT(SUM(a12*a20))  11200.372548
230 SQRT(SUM(a12*a21))  9918.04865400002
231 SQRT(SUM(a12*a22))  16915.238128
232 SQRT(SUM(a12*a23))  15982.753944
233 SQRT(SUM(a12*a24))  16635.7610579999
234 SQRT(SUM(a12*a25))  27246.081
235 SQRT(SUM(a13*a13))  35456.8316499999
236 SQRT(SUM(a13*a14))  31615.978614
237 SQRT(SUM(a13*a15))  28186.848109
238 SQRT(SUM(a13*a16))  13526.222237
239 SQRT(SUM(a13*a17))  11563.622712
240 SQRT(SUM(a13*a18))  10925.280226
241 SQRT(SUM(a13*a19))  12646.815545
242 SQRT(SUM(a13*a20))  11914.802657
243 SQRT(SUM(a13*a21))  10442.413767
244 SQRT(SUM(a13*a22))  18389.073347
245 SQRT(SUM(a13*a23))  16650.118661
246 SQRT(SUM(a13*a24))  17300.353144
247 SQRT(SUM(a13*a25))  28263.199
248 SQRT(SUM(a14*a14))  39743.143113
249 SQRT(SUM(a14*a15))  31826.87447
250 SQRT(SUM(a14*a16))  14314.869258
251 SQRT(SUM(a14*a17))  12372.371951
252 SQRT(SUM(a14*a18))  11552.086147
253 SQRT(SUM(a14*a19))  13706.006375
254 SQRT(SUM(a14*a20))  13148.092779
255 SQRT(SUM(a14*a21))  11435.299683
256 SQRT(SUM(a14*a22))  17409.1899599999
257 SQRT(SUM(a14*a23))  15474.25888
258 SQRT(SUM(a14*a24))  16916.873974
259 SQRT(SUM(a14*a25))  28914.7880000001
260 SQRT(SUM(a15*a15))  42598.3066450001
261 SQRT(SUM(a15*a16))  15498.6894799999
262 SQRT(SUM(a15*a17))  12391.906664
263 SQRT(SUM(a15*a18))  11385.282553
264 SQRT(SUM(a15*a19))  13221.5709300001
265 SQRT(SUM(a15*a20))  13559.911984
266 SQRT(SUM(a15*a21))  12682.262643
267 SQRT(SUM(a15*a22))  18717.133063
268 SQRT(SUM(a15*a23))  16725.7865579999
269 SQRT(SUM(a15*a24))  16726.19317
270 SQRT(SUM(a15*a25))  30415.854
271 SQRT(SUM(a16*a16))  14467.688123
272 SQRT(SUM(a16*a17))  8606.515076
273 SQRT(SUM(a16*a18))  7199.23160699998
274 SQRT(SUM(a16*a19))  7124.59184299999
275 SQRT(SUM(a16*a20))  7487.70040000002
276 SQRT(SUM(a16*a21))  7503.505473
277 SQRT(SUM(a16*a22))  12570.6469810001
278 SQRT(SUM(a16*a23))  10584.893317
279 SQRT(SUM(a16*a24))  10529.836498
280 SQRT(SUM(a16*a25))  15595.319
281 SQRT(SUM(a17*a17))  10186.94422
282 SQRT(SUM(a17*a18))  7269.79120800002
283 SQRT(SUM(a17*a19))  7309.42473100001
284 SQRT(SUM(a17*a20))  6221.31978499998
285 SQRT(SUM(a17*a21))  6222.43007900001
286 SQRT(SUM(a17*a22))  10379.861931
287 SQRT(SUM(a17*a23))  9263.597562
288 SQRT(SUM(a17*a24))  9415.66305699997
289 SQRT(SUM(a17*a25))  13027.056
290 SQRT(SUM(a18*a18))  8837.13581799998
291 SQRT(SUM(a18*a19))  7411.94682399997
292 SQRT(SUM(a18*a20))  6218.273407
293 SQRT(SUM(a18*a21))  5803.45119200002
294 SQRT(SUM(a18*a22))  9325.75722000001
295 SQRT(SUM(a18*a23))  8491.29250099998
296 SQRT(SUM(a18*a24))  8755.65421000002
297 SQRT(SUM(a18*a25))  12450.629
298 SQRT(SUM(a19*a19))  13255.545215
299 SQRT(SUM(a19*a20))  8239.92108699999
300 SQRT(SUM(a19*a21))  6839.53079399998
301 SQRT(SUM(a19*a22))  10794.586367
302 SQRT(SUM(a19*a23))  9559.880352
303 SQRT(SUM(a19*a24))  9962.93465900001
304 SQRT(SUM(a19*a25))  15177.263
305 SQRT(SUM(a20*a20))  13428.5472860001
306 SQRT(SUM(a20*a21))  7494.37773800001
307 SQRT(SUM(a20*a22))  12158.329374
308 SQRT(SUM(a20*a23))  10077.875049
309 SQRT(SUM(a20*a24))  9964.815422
310 SQRT(SUM(a20*a25))  15451.453
311 SQRT(SUM(a21*a21))  9927.211263
312 SQRT(SUM(a21*a22))  12581.080232
313 SQRT(SUM(a21*a23))  10611.868541
314 SQRT(SUM(a21*a24))  10271.601557
315 SQRT(SUM(a21*a25))  13396.496
316 SQRT(SUM(a22*a22))  30723.54238
317 SQRT(SUM(a22*a23))  20116.8142460001
318 SQRT(SUM(a22*a24))  18230.4277920001
319 SQRT(SUM(a22*a25))  23587.098
320 SQRT(SUM(a23*a23))  22291.559957
321 SQRT(SUM(a23*a24))  16939.468623
322 SQRT(SUM(a23*a25))  20556.627
323 SQRT(SUM(a24*a24))  20814.907172
324 SQRT(SUM(a24*a25))  20602.278
325 SQRT(SUM(a25*a25))  34307

Answer 1

您可以按照 Derek 的建议进行操作，或者（我认为这是更好的方法）您可以更改数据库设计以包含 2 个表：

CREATE TABLE [Keys] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] varchar(10) NOT NULL,
    PRIMARY KEY CLUSTERED (Id)
)

CREATE TABLE [Values] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [KeyId] int NOT NULL,
    [Value] float NOT NULL,
    PRIMARY KEY CLUSTERED (Id)
)

在这种情况下，您的查询将类似于

SELECT
        V.KeyId AS Id,
        'SQRT(SUM(' + K.Name + '*' + K.Name + '))' AS Name,
        SQRT(SUM(V.Value * V.Value)) AS Value
    FROM Values V INNER JOIN Keys K ON V.KeyId = K.Id
    GROUP BY KeyId

---

编辑：尝试一下。

--DROP TABLE [Values]
--DROP TABLE [Keys]

CREATE TABLE [Keys] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] varchar(100) NOT NULL,
    PRIMARY KEY CLUSTERED (Id)
)

CREATE TABLE [Values] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [KeyId] int NOT NULL,
    [Value] float NOT NULL,
    PRIMARY KEY CLUSTERED (Id)
)

INSERT INTO Keys (Name)
    SELECT SUBSTRING(COLUMN_NAME, 0, 100)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MyTable'; -- Change this to your current table's name

SELECT * FROM MyTable -- Change this to your current table's name

DECLARE @i int
DECLARE @iMax int

SET @i = 1;
SELECT @iMax = COUNT(*) FROM Keys

WHILE (@i <= @iMax)
BEGIN
    DECLARE @column varchar(100)
    SELECT @column = Name FROM Keys WHERE Id = @i;

    DECLARE @sql nvarchar(max)
    SET @sql = 'INSERT INTO [Values] (KeyId, Value)
            SELECT ' + CONVERT(nvarchar, @i) + ', A.' + @column + '
            FROM MyTable A'; -- Change this to your current table's name

    EXEC sp_executesql @sql;

    SET @i = @i + 1
END

SELECT * FROM [Keys]
SELECT * FROM [Values]

Answer 2

下面的查询动态生成由您所需的 UNIONED 语句组成的 SQL 语句。然后，您只需在生成的语句上运行 exec sp_executesql 并将结果插入到临时表中。

create table #temp (
    ID int identity,
    Name varchar(100),
    Value decimal(18,6))

declare @sql nvarchar(max)

select
    @sql = coalesce(
        @sql + '
    UNION ALL SELECT ''SQRT(SUM(' + a.COLUMN_NAME + '*' + b.COLUMN_NAME + '))'', 
                    SQRT(SUM(' + a.COLUMN_NAME + '*' + b.COLUMN_NAME + ')) FROM myTable',
        'SELECT ''SQRT(SUM(' + a.COLUMN_NAME + '*' + b.COLUMN_NAME + '))'', 
                    SQRT(SUM(' + a.COLUMN_NAME + '*' + b.COLUMN_NAME + ')) FROM myTable')
from
    information_schema.COLUMNS a
    cross join information_schema.COLUMNS b
where
    a.TABLE_NAME = 'myTable'
    and b.TABLE_NAME = 'myTable'
    and CONVERT(int, substring(a.COLUMN_NAME, 2, len(a.COLUMN_NAME) - 1)) <=
        CONVERT(int, substring(b.COLUMN_NAME, 2, len(b.COLUMN_NAME) - 1))
order by
    CONVERT(int, substring(a.COLUMN_NAME, 2, len(a.COLUMN_NAME) - 1)),
    CONVERT(int, substring(b.COLUMN_NAME, 2, len(b.COLUMN_NAME) - 1))


--print @sql

insert into #temp(Name,Value)
EXEC sp_executesql @sql


--drop table #temp