SQL SERVER 实现多行转多列

--测试数据

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'' union all

select 2,1,N'ext_b',N'职称',N'副级' union all

select 3,2,N'ext_1',N'工龄',N'' 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) 

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[];

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[].Rows) {

    ar.Add(crow[]);

    tb.Columns.Add(crow[].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;

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

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[].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[];

//此处单纯的SQL语句拼接，也可以再次优化实现动态参数化。