SQL SERVER 实现多行转多列

有这样一个需求，一个表单主表，一个扩展列表，查询的时候要把扩展列表中的多行转成主表多列。

比如

dt_zhubiao [主表]

id	type	title
1	1	表单1-1
2	1	表单1-2
3	2	表单2-1
4	2	表单2-2

dt_kuozhanbiao [扩展表]

id	formid	name	title	value
1	1	ext_a	工龄	18
2	1	ext_b	职称	副级
3	2	ext_a	工龄	20
4	2	ext_b	职称	正级
5	3	ext_2a	字段1	值1
6	3	ext_2b	字段2	值2
7	3	ext_2c	字段3	值3
8	4	ext_2a	字段1	值21
9	4	ext_2b	字段2	值22
10	4	ext_2c	字段3	值23

查询时，会根据dt_zhubiao表的type来查询，type字段一样时，dt_kuozhanbiao表条数和name都会一致，value不一致。

想要的结果如下：

查询type=1时，select * from dt_zhubiao where type = 1 ... 

id	type	title	ext_a	ext_b
1	1	表单1-1	18	副级
2	1	表单1-2	20	正级

查询type=2时，select * from dt_zhubiao where type =2 ...

id	type	title	ext_2a	ext_2b	ext_2c
3	2	表单2-1	值1	值2	值3
4	2	表单2-2	值21	值22	值23

 

那么问题来了，基于 select * from dt_zhubiao where type = ? 基础sql语句，如何生成这种查询结果 ？ 

这个问题应该多用于动态表单，之前自己尝试过join 、union去解决，都总差那么点意思。 -:)

后面去多个论坛发帖挨个问了个遍，终于寻到解决办法了。sql 代码如下：

--测试数据
if not object_id(n'tempdb..#主表') is null
    drop table #主表
go
create table #主表([id] int,[type] int,[title] nvarchar(25))
insert #主表
select 1,1,n'表单1-1' union all
select 2,1,n'表单1-2' union all
select 3,2,n'表单2-1' union all
select 4,2,n'表单2-2'
go
if not object_id(n'tempdb..#扩展表') is null
    drop table #扩展表
go
create table #扩展表([id] int,[formid] int,[name] nvarchar(26),[title] nvarchar(23),[value] nvarchar(22))
insert #扩展表
select 1,1,n'ext_a',n'工龄',n'18' union all
select 2,1,n'ext_b',n'职称',n'副级' union all
select 3,2,n'ext_1',n'工龄',n'18' union all
select 4,2,n'ext_b',n'职称',n'正级' union all
select 5,3,n'ext_2a',n'字段1',n'值1' union all
select 6,3,n'ext_2b',n'字段2',n'值2' union all
select 7,3,n'ext_2c',n'字段3',n'值3'union all
select 8,4,n'ext_2a',n'字段1',n'值1' union all
select 9,4,n'ext_2b',n'字段2',n'值2' union all
select 10,4,n'ext_2c',n'字段3',n'值3'
go
--测试数据结束
declare @sql varchar(max)
set @sql = 'select #主表.id,#主表.type,#主表.title'
select  @sql = @sql + ',max(case name when ''' + name
        + ''' then [value] else null end)[' + name + ']'
from    ( select distinct name from #扩展表 join #主表 on  formid in (select id from #主表 where type=2)
        ) a
set @sql = @sql
    + ' from #扩展表 join #主表 on  formid =#主表.id  where type=2 group by #主表.id,#主表.type,#主表.title'
exec(@sql) 

至此已经解决了我的问题，但是对于实际的项目运用还是缺少点什么，比如 分页、条件筛选。那么得在此基础上稍微修改一下，这个简单我自己会做了  -:) 。

declare @sql varchar(max)
set @sql = 'with tb as (select row_number() over(order by  #主表.id ) as rindex,#主表.id,#主表.type,#主表.title'
select  @sql = @sql + ',max(case name when ''' + name
        + ''' then [value] else null end)[' + name + ']'
from    ( select distinct name from #扩展表 join #主表 on  formid in (select id from #主表 where type=2)
        ) a
set @sql = @sql
    + ' from #扩展表 join #主表 on  formid =#主表.id  where type=2 group by #主表.id,#主表.type,#主表.title); select * from tb where ext_2b =''值1'' and rindex between  1 and 10 '  --拼接with 及查询条件
exec(@sql) 

到这自我感觉应该差不多了，再改改做成个存储过程应该可以用了，但是吧，想着用程序也去实现一遍，看看哪种实行起来更方便 -:) 

程序实现两种思路，一种组装table：先结合分页、条件筛选等查出需要的主表数据集得到一个datatable，然后给datatable动态去添加对应列，然后循环去赋值（这种情况就无法实现针对扩展字段进行排序）。

datatable tb = dbhelpersql.query("with tb as (select row_number over(order by #主表.id) as rindex,* from #主表 where id in (select formid from #扩展表 where value ='值2') adn type=2 );select * from tb where rindex between 1 and 10").tables[0];
var ar = new system.collections.arraylist();
foreach (datarow crow in dbhelpersql.query("select name from #扩展表 where formid in (select id from #主表 where type = 2)").tables[0].rows) {
    ar.add(crow[0]);
    tb.columns.add(crow[0].tostring(), typeof(string));
}
//这里可以一次性加载tb数据集中包含的所有#扩展表数据，然后在内存中进行操作赋值，如下循环查询数据库赋值只是为了写的方便
for (var i =0; i <tb.rows.count;i++) {
    var formid = tb.rows[i]["id"];
    foreach (var ari in ar) {
        tb.rows[i][ari.tostring()] = dbhelpersql.getsingle(string.format("select value from #临时表 where formid ={0} and name ='{1}'",formid,ari));
    }
 }
 return tb;

第二种就是组装sql语句啦，但是不在程序中组装之前的@sql字符串，但是还是参考之前的sql代码思路，然后转成程序代码思路。在之前sql实现的代码中，最后的exec(@sql)前一行加上打印@sql的语句，就会得到最后执行的sql语句

select #主表.id,#主表.type,#主表.title
,max(case name when 'ext_2a' then [value] else null end)[ext_2a]
,max(case name when 'ext_2b' then [value] else null end)[ext_2b]
,max(case name when 'ext_2c' then [value] else null end)[ext_2c]
 from #扩展表 join #主表 on  formid =#主表.id  where type=2 group by #主表.id,#主表.type,#主表.title

基于这个sql语句用程序去拼接最终的sql语句执行（包括分页、条件筛选）。

string sql=" with tb as (select row_number over(order by #主表.id) rindex, #主表.id,#主表.type,#主表.title";
foreach (datarow crow in dbhelpersql.query("select name from #扩展表 where formid in (select id from #主表 where type = 2) group by name").tables[0].rows) {
    sql +=",max(case name when '"+crow["name"]+"' then [value] else null end) "+crow["name"];
}
sql+=" from #扩展表 join #主表 on formid = #主表.id where type = 2 group by #主表.id,#主表.type,#主表.title); select * from tb where ext_2a ='值1' and rindex between 1 and 10 ";
return dbhelpersql.query(sql).tables[0];
//此处单纯的sql语句拼接，也可以再次优化实现动态参数化。

这种情况是可以实现所有的列都能进行排序。 目前来讲，还只是去实现这个功能，还没有考虑性能优化之类的。

论坛原咨询帖： 。 记于此以作备份，好记性不如烂笔头。 -:)