GO
alter proc [zsp_BranchsData]
as
begin
/*
CREATE TABLE [原始机构数据]
(
[序号] [varchar](50) NULL,
[一级分行号] [varchar](50) NULL,
[一级分行名称] [varchar](50) NULL,
[二级分行号] [varchar](50) NULL,
[二级分行名称] [varchar](50) NULL,
--[上级机构号] [varchar](50) NULL,
--[上级机构名称] [varchar](50) NULL,
--[责任中心号] [varchar](50) NULL,
--[责任中心名称] [varchar](50) NULL
)
*/
SELECT
--GROUPING([一级分行号])
--,
[一级分行号] =
iif
(
GROUPING([一级分行号]) = 1
, '合计:'
, [一级分行号]
) ,
[一级分行名称] =
iif
(
GROUPING([一级分行号]) = 1
,
cast
(
count(distinct [一级分行号]) as varchar
)
, max([一级分行名称])
) ,
[二级分行数量] = count(distinct [二级分行号])
FROM
[原始机构数据]
group by
--[一级分行号]
rollup
(
[一级分行号]
)
order by
GROUPING([一级分行号])
, [一级分行号]
select
[二级分行号] ,
[二级分行名称] = max([二级分行名称]) ,
[一级分行号] = max([一级分行号]) ,
[一级分行名称] = max([一级分行名称])
from
[原始机构数据]
group by
[二级分行号]
order by
3
;with T
as
(
SELECT
[一级分行组] = GROUPING([一级分行号]) ,
[全辖一级分行序号] = DENSE_RANK()
over
(
order by
[一级分行号]
) - 1 ,
[一级分行号] =
iif
(
GROUPING([一级分行号]) = 1
, '全部合计:'
, [一级分行号]
) ,
[一级分行名称] =
case
when
GROUPING([一级分行号]) = 1
then
cast(count(distinct [一级分行号]) as varchar)
when
GROUPING([二级分行号]) = 1
and GROUPING([一级分行号]) = 0
then
max([一级分行名称]) + ' 分组'
else
max([一级分行名称])
end ,
[二级分行组] = GROUPING([二级分行号]) ,
[辖内二级分行序号] = ROW_NUMBER()
over
(
partition by
[一级分行号]
order by
[二级分行号]
) - 1 ,
[二级分行号] =
case
when
GROUPING([一级分行号]) = 1
and GROUPING([二级分行号]) = 1
then
'全部合计:'
when
GROUPING([二级分行号]) = 1
then
'分组小计:'
else
[二级分行号]
end ,
[二级分行名称] =
iif
(
GROUPING([二级分行号]) = 1
, cast(count(distinct [二级分行号]) as varchar)
, max([二级分行名称])
)
FROM
[原始机构数据]
group by
--[一级分行号]
rollup
(
[一级分行号]
, [二级分行号]
)
)
select
--[一级分行组] ,
[全辖一级分行序号] ,
[一级分行号] ,
[一级分行名称] ,
--[二级分行组] ,
[辖内二级分行序号] ,
[二级分行号] ,
[二级分行名称]
from
T
order by
[一级分行号] ,
[一级分行组] ,
[二级分行号] ,
[二级分行组]
end
|