需求
建表脚本
1 CREATE TABLE [dbo].[A]( 2 [dm] [varchar](5) NOT NULL, 3 [dmmc] [varchar](20) NULL 4 ) ON [PRIMARY] 5 6 GO 7 CREATE TABLE [dbo].[B]( 8 [xh] [varchar](5) NOT NULL, 9 [km01] [varchar](25) NULL, 10 [km02] [varchar](25) NULL, 11 [km03] [varchar](25) NULL 12 ) ON [PRIMARY]
1 USE [test] 2 GO 3 /****** Object: StoredProcedure [dbo].[sp_pivotA] Script Date: 2016/4/22 19:07:47 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 9 ALTER PROCEDURE [dbo].[sp_pivotA] 10 11 AS 12 BEGIN 13 SET NOCOUNT ON; 14 declare @sql varchar(8000) 15 select @sql= LEFT(t,LEN(t)-1) 16 from 17 ( 18 SELECT distinct ( select '['+dm+'],' from A FOR XML PATH('') )as t from A as d 19 )e 20 declare @sql2 varchar(8000) 21 set @sql2='SELECT ''学号'' as''xh'',* 22 23 FROM A 24 pivot(MAX(A.dmmc) FOR dm IN('+@sql+') )as P' 25 26 exec(@sql2) 27 END
调用 存储过程
1 DROP TABLE #tmp1 2 go 3 select * into #tmp1 from B where 1=2 4 insert INTO #tmp1 EXEC [dbo].[sp_pivotA] 5 6 select * from #tmp1 7 union all 8 select * from B
如果想让km01,...显示成中文名称,也就是列名显示成第一行的那些中文，也可以 把B表先逆透视转换一下 然后去跟A表关联 然后在透视转换回来就可以 了。
