一个行转列的例子

CREATE TABLE [TABLE1] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [state] [int] NULL ,
 CONSTRAINT [PK_TABLE1] PRIMARY KEY  CLUSTERED
 (
  [id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

 

 

DECLARE @s nvarchar(4000)
SET @s='SELECT name'
SELECT @s=@s+','+QUOTENAME(state )+N'=
SUM(CASE state WHEN '+QUOTENAME(state ,N'''')+N'
then 1 else 0 end)'FROM table1 GROUP BY state
EXEC(@s+N'FROM table1 GROUP  BY name')