求高手注释下面句子,比较长,分数也会全给!因刚进,分数不多,望各牛人帮忙
还有中间的if 想用循环换掉,可尝试失败
USE [Financial-ODS-bk]
GO/****** Object: StoredProcedure [dbo].[CrossTab] Script Date: 09/19/2012 16:29:33 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO-- CREATE procedure [dbo].[CrossTab]
CREATE procedure [dbo].[CrossTab]
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherCols varchar(100),
@OrderByCols varchar(100)
)-- @OrderByCols varchar(100) = Null
AS
set nocount on
set ansi_warnings off
declare @sql varchar(1000)Select @sql = ''Select @OtherCols= isNull(', ' + @OtherCols,'')create table #pivot_columns (pivot_column_name varchar(100))Select @sql='select ''' + replace( + @PivotCol,',',''' as pivot_column_name union all select ''')+''''insert into #pivot_columns
exec(@sql)select @sql=''create table #pivot_columns_data (pivot_column_name varchar(100),pivot_column_data varchar(100))Select @PivotCol=''Select @PivotCol=min(pivot_column_name) from #pivot_columnsWhile @PivotCol>''
Begin
insert into #pivot_columns_data(pivot_column_name,pivot_column_data)
exec
(
'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data from
('+
@select
+'
) T'
)
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol
end if (select count(1) from #pivot_columns_data where pivot_column_data = '20120101') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120101')if (select count(1) from #pivot_columns_data where pivot_column_data = '20120201') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120201') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120301') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120301') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120401') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120401') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120501') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120501') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120601') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120601') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120701') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120701') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120801') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120801') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120901') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120901') if (select count(1) from #pivot_columns_data where pivot_column_data = '20121001') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20121001') if (select count(1) from #pivot_columns_data where pivot_column_data = '20121101') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20121101')
if (select count(1) from #pivot_columns_data where pivot_column_data = '20121201') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20121201')
select
@sql = @sql + ', ' +
replace(
replace(
@Summaries,'(','(CASE WHEN ' + Pivot_Column_name + '=''' +
pivot_column_data + ''' THEN '
),
')[', ' END) as [' + pivot_column_data
)
from #pivot_columns_data
order by pivot_column_namedeclare @sqlGroupBy varchar(3000)set @sqlGroupBy = 'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy
if @OrderByCols <> ''
set @sqlGroupBy = @sqlGroupBy + ' order by ' + @OrderByCols if @OrderByCols <> ''
exec
(
'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy + ' order by ' + @OrderByCols
)
else
exec
(
'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy
) -- + ' order by ' + @OrderByCols
drop table #pivot_columns
drop table #pivot_columns_dataset nocount off
set ansi_warnings on
GO
还有中间的if 想用循环换掉,可尝试失败
USE [Financial-ODS-bk]
GO/****** Object: StoredProcedure [dbo].[CrossTab] Script Date: 09/19/2012 16:29:33 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO-- CREATE procedure [dbo].[CrossTab]
CREATE procedure [dbo].[CrossTab]
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherCols varchar(100),
@OrderByCols varchar(100)
)-- @OrderByCols varchar(100) = Null
AS
set nocount on
set ansi_warnings off
declare @sql varchar(1000)Select @sql = ''Select @OtherCols= isNull(', ' + @OtherCols,'')create table #pivot_columns (pivot_column_name varchar(100))Select @sql='select ''' + replace( + @PivotCol,',',''' as pivot_column_name union all select ''')+''''insert into #pivot_columns
exec(@sql)select @sql=''create table #pivot_columns_data (pivot_column_name varchar(100),pivot_column_data varchar(100))Select @PivotCol=''Select @PivotCol=min(pivot_column_name) from #pivot_columnsWhile @PivotCol>''
Begin
insert into #pivot_columns_data(pivot_column_name,pivot_column_data)
exec
(
'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data from
('+
@select
+'
) T'
)
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol
end if (select count(1) from #pivot_columns_data where pivot_column_data = '20120101') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120101')if (select count(1) from #pivot_columns_data where pivot_column_data = '20120201') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120201') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120301') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120301') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120401') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120401') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120501') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120501') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120601') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120601') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120701') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120701') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120801') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120801') if (select count(1) from #pivot_columns_data where pivot_column_data = '20120901') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20120901') if (select count(1) from #pivot_columns_data where pivot_column_data = '20121001') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20121001') if (select count(1) from #pivot_columns_data where pivot_column_data = '20121101') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20121101')
if (select count(1) from #pivot_columns_data where pivot_column_data = '20121201') = 0
insert into #pivot_columns_data(pivot_column_name,pivot_column_data) values ('fromDateString', '20121201')
select
@sql = @sql + ', ' +
replace(
replace(
@Summaries,'(','(CASE WHEN ' + Pivot_Column_name + '=''' +
pivot_column_data + ''' THEN '
),
')[', ' END) as [' + pivot_column_data
)
from #pivot_columns_data
order by pivot_column_namedeclare @sqlGroupBy varchar(3000)set @sqlGroupBy = 'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy
if @OrderByCols <> ''
set @sqlGroupBy = @sqlGroupBy + ' order by ' + @OrderByCols if @OrderByCols <> ''
exec
(
'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy + ' order by ' + @OrderByCols
)
else
exec
(
'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy
) -- + ' order by ' + @OrderByCols
drop table #pivot_columns
drop table #pivot_columns_dataset nocount off
set ansi_warnings on
GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货