递增的列表写法求助
大概结构如下
A B
1 9
1 9
1 29
2 9
2 39
2 9
.. ...
得出如下
A 9 19 29 39 49....99
1 2 0 1 0 0...
2 2 0 0 1 0....
表列B 为9以10为递增 到99
当A＝1 时 统计当时的个数 如当B＝9 统计数为 2 无则为0
...
谢谢帮助..

[解决办法]

SQL code

--创建测试表及数据create table tablename_tz(a int,b int)insert into tablename_tzselect 1,9 union allselect 1,9 union allselect 1,29 union allselect 2,9 union allselect 2,39 union allselect 2,9declare @sql varchar(8000) set @sql = 'select a 'declare @i int set @i = 1while ( @i <= 10 )     begin        set @sql = @sql + ',[' + ltrim(( @i - 1 ) * 10 + 9)            + ']=(select count(1) from tablename_tz where a= t.a and b='            + +ltrim(( @i - 1 ) * 10 + 9) + +')'        set @i = @i + 1    endset @sql = @sql + 'from tablename_tz t group by a'exec (@sql)/*A           9           19          29          39          49          59          69          79          89          99----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------1           2           0           1           0           0           0           0           0           0           02           2           0           0           1           0           0           0           0           0           0*/
[解决办法]
SQL code
create table  t (A int,B int)insert into Tselect 1,9 union allselect 1,9 union allselect 1,29 union allselect 2,9 union allselect 2,39 union allselect 2,9godeclare @str varchar(max)=''select @str=@str+',['+cast((number) as varchar(10))+']=(select count(1) from t where A=tb.A and b='+cast((number) as varchar(10))+')' +CHAR(10)from  master..spt_values b where b.type='p' and b.number<100 AND B.number%10=9set @str='select A'+@str+' from t tb group by A'EXEC (@str)A    9    19    29    39    49    59    69    79    89    991    2    0    1    0    0    0    0    0    0    02    2    0    0    1    0    0    0    0    0    0(2 行受影响)