1 SELECT * INTO ##GET
3 FROM
4 (
5 SELECT
6 *
7 FROM
8 (
9 SELECT
10 CASE
11 WHEN (GROUPING([档案号]) = 1) THEN
12 '合计'
13 ELSE
14 [档案号]
15 END AS '档案号',
16 CASE
17 WHEN (GROUPING([系列]) = 1) THEN
18 '合计'
19 ELSE
20 [系列]
21 END AS '系列',
22 CASE
23 WHEN (GROUPING([店长]) = 1) THEN
24 '合计'
25 ELSE
26 [店长]
27 END AS '店长',
28 SUM (剩余次数) AS '总剩余',
29 CASE
30 WHEN (GROUPING([店名]) = 1) THEN
31 '合计'
32 ELSE
33 [店名]
34 END AS '店名'
35 FROM
36 ##PudianCard
37 GROUP BY
38 [档案号],
39 [店名],
40 [店长],
41 [系列] WITH cube
42 HAVING
43 GROUPING([店名]) != 1
44 AND GROUPING([档案号]) = 1 --AND GROUPING([系列]) = 1
45 ) AS M
46 UNION ALL
47 (
48 SELECT
49 *
50 FROM
51 (
52 SELECT
53 CASE
54 WHEN (GROUPING([档案号]) = 1) THEN
55 '合计'
56 ELSE
57 [档案号]
58 END AS '档案号',
59 CASE
60 WHEN (GROUPING([系列]) = 1) THEN
61 '合计'
62 ELSE
63 [系列]
64 END AS '系列',
65 CASE
66 WHEN (GROUPING([店长]) = 1) THEN
67 '合计'
68 ELSE
69 [店长]
70 END AS '店长',
71 SUM (剩余次数) AS '总剩余',
72 CASE
73 WHEN (GROUPING([店名]) = 1) THEN
74 '合计'
75 ELSE
76 [店名]
77 END AS '店名'
78 FROM
79 ##PudianCard
80 GROUP BY
81 [档案号],
82 [店名],
83 [店长],
84 [系列] WITH cube
85 HAVING
86 GROUPING([店名]) != 1
87 AND GROUPING([店长]) != 1
88 ) AS P
89 )
90 UNION ALL
91 (
92 SELECT
93 *
94 FROM
95 (
96 SELECT
97 CASE
98 WHEN (GROUPING([档案号]) = 1) THEN
99 '合计'
100 ELSE
101 [档案号]
102 END AS '档案号',
103 CASE
104 WHEN (GROUPING([系列]) = 1) THEN
105 '合计'
106 ELSE
107 [系列]
108 END AS '系列',
109 CASE
110 WHEN (GROUPING([店长]) = 1) THEN
111 '合计'
112 ELSE
113 [店长]
114 END AS '店长',
115 SUM (剩余次数) AS '总剩余',
116 CASE
117 WHEN (GROUPING([店名]) = 1) THEN
118 '合计'
119 ELSE
120 [店名]
121 END AS '店名'
122 FROM
123 ##PudianCard
124 GROUP BY
125 [档案号],
126 [店名],
127 [店长],
128 [系列] WITH cube
129 HAVING
130 GROUPING([店名]) != 1
131 AND GROUPING([店长]) != 1
132 ) AS W
133 )
134 UNION ALL
135 (
136 SELECT
137 *
138 FROM
139 (
140 SELECT
141 CASE
142 WHEN (GROUPING([档案号]) = 1) THEN
143 '合计'
144 ELSE
145 [档案号]
146 END AS '档案号',
147 CASE
148 WHEN (GROUPING([系列]) = 1) THEN
149 '合计'
150 ELSE
151 [系列]
152 END AS '系列',
153 CASE
154 WHEN (GROUPING([店长]) = 1) THEN
155 '合计'
156 ELSE
157 [店长]
158 END AS '店长',
159 SUM (剩余次数) AS '总剩余',
160 CASE
161 WHEN (GROUPING([店名]) = 1) THEN
162 '合计'
163 ELSE
164 [店名]
165 END AS '店名'
166 FROM
167 ##PudianCard
168 GROUP BY
169 [档案号],
170 [店名],
171 [店长],
172 [系列] WITH cube
173 HAVING
174 GROUPING([店名]) = 1
175 AND GROUPING([店长]) = 1
176 AND GROUPING([档案号]) = 1
177 ) AS K
178 )
179 ) AS T
2、rollup:功能跟cube相似
3、将某一列的数据作为列名,动态加载,使用存储过程,拼接字符串
DECLARE @st nvarchar (MAX) = '';
SELECT
@st =@st + 'max(case when [系列]=''' + CAST ([系列] AS VARCHAR) + ''' then [总剩余] else null end ) as [' + CAST ([系列] AS VARCHAR) + '],'
FROM
##GET
GROUP BY
[系列];
print @st;
4、根据某一列分组,分别建表
SELECT
'select ROW_NUMBER() over(order by [卡项] desc) as [序号], [会员],[档案号],[卡项],[剩余次数],[员工],[店名] into ' + ltrim([店名]) + ' from 查询 where [店名]=''' + [店名] + ''' ORDER BY [卡项] desc'
FROM
查询
GROUP BY
[店名]