· 6 years ago · Jun 25, 2019, 08:06 PM
1SELECT A, B
2FROM (
3 SELECT
4 MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
5 , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
6 , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
7 , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
8 , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
9 , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
10 , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
11 , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
12 FROM dbo.PARALLEL_ZONE_REPRO
13) q
14UNPIVOT(B FOR A IN (
15 VAL1
16 ,VAL2
17 ,VAL3
18 ,VAL4
19 ,VAL5
20 ,VAL6
21 ,VAL7
22 ,VAL16
23)) U
24OPTION (MAXDOP 4);
25
26DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;
27
28CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
29 ID BIGINT,
30 FILLER VARCHAR(100)
31);
32
33INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
34SELECT
35 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
36, REPLICATE('Z', 100)
37FROM master..spt_values t1
38CROSS JOIN master..spt_values t2;
39
40SELECT A, B
41FROM
42(
43 SELECT A
44 , MAX(
45 CASE
46 WHEN A = 'VAL1' THEN VAL1
47 WHEN A = 'VAL2' THEN VAL2
48 WHEN A = 'VAL3' THEN VAL3
49 WHEN A = 'VAL4' THEN VAL4
50 WHEN A = 'VAL5' THEN VAL5
51 WHEN A = 'VAL6' THEN VAL6
52 WHEN A = 'VAL7' THEN VAL7
53 WHEN A = 'VAL16' THEN VAL16
54 ELSE NULL
55 END
56 ) B
57 FROM (
58 SELECT
59 MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
60 , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
61 , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
62 , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
63 , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
64 , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
65 , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
66 , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
67 FROM dbo.PARALLEL_ZONE_REPRO
68 ) q
69 CROSS APPLY (
70 VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
71 ('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
72 ) ca (A)
73 GROUP BY A
74) q
75WHERE q.B IS NOT NULL
76OPTION (MAXDOP 4);