sql server 列转行

两个表
 
表1：tb_Site
 
 
 
 
CREATE TABLE [dbo].[tb_Site] ( 
    [sid] [int] IDENTITY (1, 1) NOT NULL , 
    [sname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL  
) ON [PRIMARY] 
GO 
 
 
 
 
表2：tb_qx
 
 
 
 
CREATE TABLE [dbo].[tb_qx] ( 
    [qid] [int] IDENTITY (1, 1) NOT NULL , 
    [qname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL  
) ON [PRIMARY] 
GO 
 
 
动态SQL
 
 
 
 
 
 
declare @sql1 varchar(8000) 
set @sql1='select c.sname ' 
select @sql1=@sql1+', max(case qname when'''+qname+'''then qname else ''0'' end) ['+qname+']' from tb_qx  
select @sql1=@sql1+' from (select * from tb_Site left join (select * from tb_qx) d on 1=1) c group by c.sname' 
exec(@sql1) 
 
 
摘自 李晓光的专栏