比較複雜的動态SQL語句功能一例

今天在論壇裡見到一個關于動态SQL問題，覺得有點意思，于是解答了一下，順便把我的解決方案轉到這裡。

----------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------

Question:

本人想做一個組合統計，誰可以告訴我怎麼實作。

表如下形式：

id    sex    school    government   area 

-----------------------------------------

***    男     中學        團員      北京

***    男     大學        黨員      上海

***    女     國小        群衆      北京

***    女     中學        群衆      天津

***    男     國小        黨員      上海

***    女     大學        團員      北京

-------------------------------------------

現在想統計出一個清單，清單橫軸和縱軸 分别可由以上4個字段中的一個或幾個組合而成。實作橫縱都可以動态定制。

比如：

或着

又或者

又或者

----------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------

Answer:

--生成測試資料

create table t(id varchar(6),sex varchar(6),school varchar(6),government varchar(6),area varchar(6))

insert into t select '***','男','中學','團員','北京'

insert into t select '***','男','大學','黨員','上海'

insert into t select '***','女','國小','群衆','北京'

insert into t select '***','女','中學','群衆','天津'

insert into t select '***','男','國小','黨員','上海'

insert into t select '***','女','大學','團員','北京'

go

--建立存儲過程

[email protected]_col：用于橫向排列的列，以','作為結束符

[email protected]_row：使用者縱向分組的列，以','作為結束符

create procedure sp_test(@str_col varchar(80),@str_row varchar(80))

as

begin

    declare @sql  varchar(8000),

            @str1 varchar(8000),

            @str2 varchar(8000),

            @temp nvarchar(4000),

            @col  varchar(20),

            @row  varchar(20)

    set @sql =''

    set @str1=''

    while charindex(',',@str_col)>0

    begin

        set @col=left(@str_col,charindex(',',@str_col)-1)

        set @str_col=stuff(@str_col,1,charindex(',',@str_col),'')

        set @temp=N'set @s=''''

                    select @[email protected]+'',[''+'[email protected]+'+'']=sum(case '[email protected]+' when ''''''+'[email protected]+'+'''''' then 1 else 0 end) '' 

                    from t group by '[email protected]

        exec sp_executesql @temp,N'@s varchar(8000) out',@str2 out

        set @[email protected][email protected]

    end

    while charindex(',',@str_row)>0

    begin

        set @row=left(@str_row,charindex(',',@str_row)-1)

        set @str_row=stuff(@str_row,1,charindex(',',@str_row),'')

        set @[email protected]+' union all select '[email protected]+' as 項目'[email protected]+' from t group by '[email protected]

    end

    set @sql=stuff(@sql,1,11,'')

    exec(@sql)

end

go

--執行測試

exec sp_test 'school,sex,','government,area,'

go

--輸出測試結果

--删除測試環境

drop table t

drop procedure sp_test

go