SQL語句(行列轉(zhuǎn)換以及字符串求和)

create table tempAB(A varchar(10),B varchar(10))
    insert into tempAB values('a1','b1')
    insert into tempAB values('a2','b1')
    insert into tempAB values('a3','b1')
    insert into tempAB values('a4','b2')
    insert into tempAB values('a5','b2')
    insert into tempAB values('a6','b2')
    //新建一個表，以下代碼可以返回B的值為b2,考試,大提示所有對應(yīng)的A的值相加起來的結(jié)果
    首先就是把A列中符合條件的行轉(zhuǎn)換為列，col+A的值為列名，
    再把各列相加
    declare @sql varchar(4000)
    set @sql = 'select '''''
    select @sql=@sql+'+table'+A+'.'+'col'+A
    from (select distinct A from tempAB where B='b2') as e
    select @sql=@sql+' from '
    select @sql=@sql+'(select a.'+'col'+A+' from(select (case A when '''+A+''' then A end) '+'col'+A+' '+
    'from tempAB) a inner join (select (case A when '''+A+''' then A end) '+'col'+A+' '+
    'from tempAB) b on a.col'+A+'=b.col'+A+') '+'table'+A+','
    from (select distinct A from tempAB where B='b2') as f
    select @sql=substring(@sql,0,len(@sql))
    print @sql
    exec(@sql)