这句动态sql语句应该怎么写?
tableA

Id State Type1 Type2 Type3
1 3 2 4 8
2 1 1 3 5
3 3 1 3 5
4 2 1 3 6
5 1 2 4 8
6 1 2 4 8
7 2 2 4 8


tbState
Id StateName
1 StateA
2 StateB
3 StateC



tbType
Id TypeName ParentId TypeCorresponding
1 TypeA 0 TypeH1
2 TypeB 0 TypeH1
3 TypeAA 1 TypeH2
4 TypeBA 2 TypeH2
5 TypeAAA 3 TypeH3
6 TypeAAB 3 TypeH3
7 TypeBAA 4 TypeH3
8 TypeBAB 4 TypeH3

tbType现在显示的内容相当于3个联动的下拉框,当下拉框选择TypeA时,显示1个选项TypeAA,选择TypeB时,显示1个选项TypeBA,然后当下拉框选择TypeAA时,显示2个选项TypeAAA,TypeAAB,当下拉框选择TypeBA时,显示2个选项TypeBAA,TypeBAB
字段TypeCorresponding显示的内容有2层含义:
1.表示数据的归类
2.TypeH1对应tableA的Type1,也就是说tableA的Type1字段的数据只能显示1和2,
TypeH2对应tableA的Type2,也就是说tableA的Type2字段的数据只能显示3和4
TypeH3对应tableA的Type3,也就是说tableA的Type3字段的数据只能显示5,6,7,8


我想运用动态sql语句显示出这样一个效果:

当在页面上选择tbType的TypeA时,显示

类型 StateA StateB StateC
TypeAA 1 1 1



当在页面上选择tbType的TypeB时,显示

类型 StateA StateB StateC
TypeBA 2 1 1


当在页面上选择tbType的TypeAA时,显示

类型 StateA StateB StateC
TypeAAA 1 0 1
TypeAAB 0 1 0


以此类推,



请问这句动态sql语句应该怎么写?

[解决办法]

SQL code

--> 测试数据：[tableA]if object_id('[tableA]') is not null drop table [tableA]create table [tableA]([Id] int,[State] int,[Type] int)insert [tableA]select 1,3,4 union allselect 2,2,5 union allselect 3,1,2 union allselect 4,2,3 union allselect 5,3,3 union allselect 6,1,2 union allselect 7,2,1--> 测试数据：[tbState]if object_id('[tbState]') is not null drop table [tbState]create table [tbState]([Id] int,[StateName] varchar(6))insert [tbState]select 1,'StateA' union allselect 2,'StateB' union allselect 3,'StateC'--> 测试数据：[tbType]if object_id('[tbType]') is not null drop table [tbType]create table [tbType]([Id] int,[TypeName] varchar(20),[ParentId] INT)insert INTO [tbType]select 1,'TypeAAB',3 union allselect 2,'TypeAAA',3 union allselect 3,'TypeAA',0 union allselect 4,'TypeD',0 union allselect 5,'TypeE',0declare @str varchar(max)set @str=''select     @str=@str+','+StateName+'=sum(case when StateName='    +QUOTENAME(StateName,'''')+' then 1 else 0 end)'from     (    select a.Id,b.StateName,c.TypeNamefrom [tableA] aleft join [tbState] bon a.State=b.Idleft join [tbType] con a.Type=c.Id    )tgroup by    StateName        DECLARE @Type VARCHAR(10)SET @Type = 'TypeAA'    exec(';WITH List AS(    SELECT tbType.* FROM tbType WHERE tbType.TypeName='''+@Type+'''    UNION ALL    SELECT tbType.* FROM List,tbType WHERE List.Id = tbType.ParentId )select TypeName'+@str+' from ('+'select a.Id,b.StateName,c.TypeName,c.Id as cidfrom [tableA] aleft join [tbState] bon a.State=b.Idleft join [tbType] con a.Type=c.Id'+')t where  t.cid IN(select List.Id from List where List.TypeName<>'''+@Type+''') group by TypeName')/*(7 行受影响)(3 行受影响)(5 行受影响)TypeName             StateA      StateB      StateC-------------------- ----------- ----------- -----------TypeAAA              2           0           0TypeAAB              0           1           0(2 行受影响)*/