利用 SQL 语句如何实现多行数据转成多列？

tohert 2019-08-01 09:35:59

...全文

812 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六 2019-08-01

打赏
举报

--测试数据

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)