0001 create temporary view courseSales as select * from values
0002 ("dotNET", 2012, 10000),
0003 ("Java", 2012, 20000),
0004 ("dotNET", 2012, 5000),
0005 ("dotNET", 2013, 48000),
0006 ("Java", 2013, 30000)
0007 as courseSales(course, year, earnings);
0008
0009 create temporary view years as select * from values
0010 (2012, 1),
0011 (2013, 2)
0012 as years(y, s);
0013
0014 create temporary view yearsWithComplexTypes as select * from values
0015 (2012, array(1, 1), map('1', 1), struct(1, 'a')),
0016 (2013, array(2, 2), map('2', 2), struct(2, 'b'))
0017 as yearsWithComplexTypes(y, a, m, s);
0018
0019
0020 SELECT * FROM (
0021 SELECT year, course, earnings FROM courseSales
0022 )
0023 PIVOT (
0024 sum(earnings)
0025 FOR course IN ('dotNET', 'Java')
0026 );
0027
0028
0029 SELECT * FROM courseSales
0030 PIVOT (
0031 sum(earnings)
0032 FOR year IN (2012, 2013)
0033 );
0034
0035
0036 SELECT * FROM (
0037 SELECT year, course, earnings FROM courseSales
0038 )
0039 PIVOT (
0040 sum(earnings), avg(earnings)
0041 FOR course IN ('dotNET', 'Java')
0042 );
0043
0044
0045 SELECT * FROM (
0046 SELECT course, earnings FROM courseSales
0047 )
0048 PIVOT (
0049 sum(earnings)
0050 FOR course IN ('dotNET', 'Java')
0051 );
0052
0053
0054 SELECT * FROM (
0055 SELECT year, course, earnings FROM courseSales
0056 )
0057 PIVOT (
0058 sum(earnings), min(year)
0059 FOR course IN ('dotNET', 'Java')
0060 );
0061
0062
0063 SELECT * FROM (
0064 SELECT course, year, earnings, s
0065 FROM courseSales
0066 JOIN years ON year = y
0067 )
0068 PIVOT (
0069 sum(earnings)
0070 FOR s IN (1, 2)
0071 );
0072
0073
0074 SELECT * FROM (
0075 SELECT course, year, earnings, s
0076 FROM courseSales
0077 JOIN years ON year = y
0078 )
0079 PIVOT (
0080 sum(earnings), min(s)
0081 FOR course IN ('dotNET', 'Java')
0082 );
0083
0084
0085 SELECT * FROM (
0086 SELECT course, year, earnings, s
0087 FROM courseSales
0088 JOIN years ON year = y
0089 )
0090 PIVOT (
0091 sum(earnings * s)
0092 FOR course IN ('dotNET', 'Java')
0093 );
0094
0095
0096 SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
0097 SELECT year y, course c, earnings e FROM courseSales
0098 )
0099 PIVOT (
0100 sum(e) s, avg(e) a
0101 FOR y IN (2012, 2013)
0102 );
0103
0104
0105 SELECT firstYear_s, secondYear_s, firstYear_a, secondYear_a, c FROM (
0106 SELECT year y, course c, earnings e FROM courseSales
0107 )
0108 PIVOT (
0109 sum(e) s, avg(e) a
0110 FOR y IN (2012 as firstYear, 2013 secondYear)
0111 );
0112
0113
0114 SELECT * FROM courseSales
0115 PIVOT (
0116 abs(earnings)
0117 FOR year IN (2012, 2013)
0118 );
0119
0120
0121 SELECT * FROM (
0122 SELECT year, course, earnings FROM courseSales
0123 )
0124 PIVOT (
0125 sum(earnings), year
0126 FOR course IN ('dotNET', 'Java')
0127 );
0128
0129
0130 SELECT * FROM (
0131 SELECT course, earnings FROM courseSales
0132 )
0133 PIVOT (
0134 sum(earnings)
0135 FOR year IN (2012, 2013)
0136 );
0137
0138
0139 SELECT * FROM (
0140 SELECT year, course, earnings FROM courseSales
0141 )
0142 PIVOT (
0143 ceil(sum(earnings)), avg(earnings) + 1 as a1
0144 FOR course IN ('dotNET', 'Java')
0145 );
0146
0147
0148 SELECT * FROM (
0149 SELECT year, course, earnings FROM courseSales
0150 )
0151 PIVOT (
0152 sum(avg(earnings))
0153 FOR course IN ('dotNET', 'Java')
0154 );
0155
0156
0157 SELECT * FROM (
0158 SELECT course, year, earnings, s
0159 FROM courseSales
0160 JOIN years ON year = y
0161 )
0162 PIVOT (
0163 sum(earnings)
0164 FOR (course, year) IN (('dotNET', 2012), ('Java', 2013))
0165 );
0166
0167
0168 SELECT * FROM (
0169 SELECT course, year, earnings, s
0170 FROM courseSales
0171 JOIN years ON year = y
0172 )
0173 PIVOT (
0174 sum(earnings)
0175 FOR (course, s) IN (('dotNET', 2) as c1, ('Java', 1) as c2)
0176 );
0177
0178
0179 SELECT * FROM (
0180 SELECT course, year, earnings, s
0181 FROM courseSales
0182 JOIN years ON year = y
0183 )
0184 PIVOT (
0185 sum(earnings)
0186 FOR (course, year) IN ('dotNET', 'Java')
0187 );
0188
0189
0190 SELECT * FROM courseSales
0191 PIVOT (
0192 sum(earnings)
0193 FOR year IN (s, 2013)
0194 );
0195
0196
0197 SELECT * FROM courseSales
0198 PIVOT (
0199 sum(earnings)
0200 FOR year IN (course, 2013)
0201 );
0202
0203
0204 SELECT * FROM (
0205 SELECT course, year, a
0206 FROM courseSales
0207 JOIN yearsWithComplexTypes ON year = y
0208 )
0209 PIVOT (
0210 min(a)
0211 FOR course IN ('dotNET', 'Java')
0212 );
0213
0214
0215 SELECT * FROM (
0216 SELECT course, year, y, a
0217 FROM courseSales
0218 JOIN yearsWithComplexTypes ON year = y
0219 )
0220 PIVOT (
0221 max(a)
0222 FOR (y, course) IN ((2012, 'dotNET'), (2013, 'Java'))
0223 );
0224
0225
0226 SELECT * FROM (
0227 SELECT earnings, year, a
0228 FROM courseSales
0229 JOIN yearsWithComplexTypes ON year = y
0230 )
0231 PIVOT (
0232 sum(earnings)
0233 FOR a IN (array(1, 1), array(2, 2))
0234 );
0235
0236
0237 SELECT * FROM (
0238 SELECT course, earnings, year, a
0239 FROM courseSales
0240 JOIN yearsWithComplexTypes ON year = y
0241 )
0242 PIVOT (
0243 sum(earnings)
0244 FOR (course, a) IN (('dotNET', array(1, 1)), ('Java', array(2, 2)))
0245 );
0246
0247
0248 SELECT * FROM (
0249 SELECT earnings, year, s
0250 FROM courseSales
0251 JOIN yearsWithComplexTypes ON year = y
0252 )
0253 PIVOT (
0254 sum(earnings)
0255 FOR s IN ((1, 'a'), (2, 'b'))
0256 );
0257
0258
0259 SELECT * FROM (
0260 SELECT course, earnings, year, s
0261 FROM courseSales
0262 JOIN yearsWithComplexTypes ON year = y
0263 )
0264 PIVOT (
0265 sum(earnings)
0266 FOR (course, s) IN (('dotNET', (1, 'a')), ('Java', (2, 'b')))
0267 );
0268
0269
0270 SELECT * FROM (
0271 SELECT earnings, year, m
0272 FROM courseSales
0273 JOIN yearsWithComplexTypes ON year = y
0274 )
0275 PIVOT (
0276 sum(earnings)
0277 FOR m IN (map('1', 1), map('2', 2))
0278 );
0279
0280
0281 SELECT * FROM (
0282 SELECT course, earnings, year, m
0283 FROM courseSales
0284 JOIN yearsWithComplexTypes ON year = y
0285 )
0286 PIVOT (
0287 sum(earnings)
0288 FOR (course, m) IN (('dotNET', map('1', 1)), ('Java', map('2', 2)))
0289 );
0290
0291
0292
0293 SELECT * FROM (
0294 SELECT course, earnings, "a" as a, "z" as z, "b" as b, "y" as y, "c" as c, "x" as x, "d" as d, "w" as w
0295 FROM courseSales
0296 )
0297 PIVOT (
0298 sum(Earnings)
0299 FOR Course IN ('dotNET', 'Java')
0300 );