TSQL查询 [英] TSQL Query
本文介绍了TSQL查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要一个类似于枢轴的查询，其中包含产品的分支数量经理。
I need a query like pivot which contains branch count of product by manager.
create table test_table
(branch varchar(50),manager varchar(50),cnt_prod float)
insert into test_table values( 'Branch 1 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 2 ', 'Manager1 ', '1 ')
insert into test_table values( 'Branch 3 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 4 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 5 ', 'Manager1 ', '1 ')
insert into test_table values( 'Branch 6 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 7 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 8 ', 'Manager1 ', '2 ')
insert into test_table values( 'Branch 9 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 10 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 11 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 12 ', 'Manager2 ', '4 ')
insert into test_table values( 'Branch 13 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 14 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 15 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 16 ', 'Manager2 ', '1 ')
insert into test_table values( 'Branch 17 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 18 ', 'Manager2 ', '4 ')
insert into test_table values( 'Branch 19 ', 'Manager3 ', '1 ')
insert into test_table values( 'Branch 20 ', 'Manager3 ', '4 ')
insert into test_table values( 'Branch 21 ', 'Manager3 ', '0 ')
insert into test_table values( 'Branch 22 ', 'Manager3 ', '0 ')
insert into test_table values( 'Branch 23 ', 'Manager3 ', '1 ')
insert into test_table values( 'Branch 24 ', 'Manager3 ', '2 ')
insert into test_table values( 'Branch 25 ', 'Manager3 ', '4 ')
insert into test_table values( 'Branch 26 ', 'Manager3 ', '10 ')
insert into test_table values( 'Branch 27 ', 'Manager3 ', '1 ')
insert into test_table values( 'Branch 28 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 29 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 30 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 31 ', 'Manager4 ', '17 ')
insert into test_table values( 'Branch 32 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 33 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 34 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 35 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 36 ', 'Manager4 ', '3 ')
insert into test_table values( 'Branch 37 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 38 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 39 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 40 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 41 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 42 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 43 ', 'Manager4 ', '4 ')
insert into test_table values( 'Branch 44 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 45 ', 'Manager4 ', '3 ')
insert into test_table values( 'Branch 46 ', 'Manager4 ', '6 ')
insert into test_table values( 'Branch 47 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 48 ', 'Manager5 ', '0 ')
insert into test_table values( 'Branch 49 ', 'Manager5 ', '4 ')
insert into test_table values( 'Branch 50 ', 'Manager5 ', '1 ')
输出
GSKR
推荐答案
create table test_table
(branch varchar(50),manager varchar(50),cnt_prod float)
insert into test_table values( 'Branch 1 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 2 ', 'Manager1 ', '1 ')
insert into test_table values( 'Branch 3 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 4 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 5 ', 'Manager1 ', '1 ')
insert into test_table values( 'Branch 6 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 7 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 8 ', 'Manager1 ', '2 ')
insert into test_table values( 'Branch 9 ', 'Manager1 ', '0 ')
insert into test_table values( 'Branch 10 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 11 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 12 ', 'Manager2 ', '4 ')
insert into test_table values( 'Branch 13 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 14 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 15 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 16 ', 'Manager2 ', '1 ')
insert into test_table values( 'Branch 17 ', 'Manager2 ', '0 ')
insert into test_table values( 'Branch 18 ', 'Manager2 ', '4 ')
insert into test_table values( 'Branch 19 ', 'Manager3 ', '1 ')
insert into test_table values( 'Branch 20 ', 'Manager3 ', '4 ')
insert into test_table values( 'Branch 21 ', 'Manager3 ', '0 ')
insert into test_table values( 'Branch 22 ', 'Manager3 ', '0 ')
insert into test_table values( 'Branch 23 ', 'Manager3 ', '1 ')
insert into test_table values( 'Branch 24 ', 'Manager3 ', '2 ')
insert into test_table values( 'Branch 25 ', 'Manager3 ', '4 ')
insert into test_table values( 'Branch 26 ', 'Manager3 ', '10 ')
insert into test_table values( 'Branch 27 ', 'Manager3 ', '1 ')
insert into test_table values( 'Branch 28 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 29 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 30 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 31 ', 'Manager4 ', '17 ')
insert into test_table values( 'Branch 32 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 33 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 34 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 35 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 36 ', 'Manager4 ', '3 ')
insert into test_table values( 'Branch 37 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 38 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 39 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 40 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 41 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 42 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 43 ', 'Manager4 ', '4 ')
insert into test_table values( 'Branch 44 ', 'Manager4 ', '0 ')
insert into test_table values( 'Branch 45 ', 'Manager4 ', '3 ')
insert into test_table values( 'Branch 46 ', 'Manager4 ', '6 ')
insert into test_table values( 'Branch 47 ', 'Manager4 ', '1 ')
insert into test_table values( 'Branch 48 ', 'Manager5 ', '0 ')
insert into test_table values( 'Branch 49 ', 'Manager5 ', '4 ')
insert into test_table values( 'Branch 50 ', 'Manager5 ', '1 ')
select ISNULL(manager,'Total') as Manager,
sum(case when cnt_prod=0 then 1 else 0 end ) [0]
, sum(case when cnt_prod=1 then 1 else 0 end ) [1]
, sum(case when cnt_prod=2 then 1 else 0 end ) [2]
, sum(case when cnt_prod=3 then 1 else 0 end ) [3]
, sum(case when cnt_prod=4 then 1 else 0 end ) [4]
, sum(case when cnt_prod=6 then 1 else 0 end ) [6]
, sum(case when cnt_prod=10 then 1 else 0 end ) [10]
, sum(case when cnt_prod=17 then 1 else 0 end ) [17]
, count(*) total
from test_table
group by GROUPING SETS ((manager),())
drop table test_table
这篇关于TSQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
