SQL行转列汇总--通用方法

行转列的通用方法：

--1、创建临时表
IF NOT EXISTS (
	SELECT
		*
	FROM
		dbo.sysobjects
	WHERE
		xtype = 'U'
	AND Name = '#devstatehis'
)
BEGIN
	CREATE TABLE #devstatehis (
		[devid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
		[devsctype] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
		[day] [INT],
		[hour] DECIMAL (19, 1)
	) ;
END 

--2、插入临时表数据
---删除临时表数据 
TRUNCATE TABLE #devstatehis; 
---插入临时表数据
INSERT INTO #devstatehis (devid,devsctype,[day],[hour])Values ('1','TYPE1',3,3.5);
INSERT INTO #devstatehis (devid,devsctype,[day],[hour])Values ('1','TYPE2',5,4.5);
INSERT INTO #devstatehis (devid,devsctype,[day],[hour])Values ('2','TYPE1',4,5.5);

--3、写行转列的存储过程(重点)
ALTER PROCEDURE [dbo].[dev_devstatehis2]
    @tableName SYSNAME , --行转列表  -----#devstatehis
    @groupColumn SYSNAME , --分组字段 ----devid
    @row2column SYSNAME ,--行变列的字段----devsctype
    @row2columnValue SYSNAME , --行变列值的字段---hour
    @sql_where NVARCHAR(MAX) --筛选条件 
AS
    BEGIN
             DECLARE @sql_str NVARCHAR(MAX)
       DECLARE @sql_col NVARCHAR(MAX)


       SET @sql_str = N'
	SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(devsctype) 
		FROM #devstatehis  GROUP BY devsctype '
      --  PRINT @sql_str


       EXEC sp_executesql @sql_str, N'@sql_col_out NVARCHAR(MAX) OUTPUT',
        @sql_col_out = @sql_col OUTPUT
       -- PRINT @sql_col
       SET @sql_str = N'
	SELECT  devhis.*,info.days,info.hours  FROM  		
		(SELECT TOP 100 PERCENT * FROM ( 
		SELECT devid,devsctype,hour FROM #devstatehis ) p PIVOT 
		(max(hour) FOR devsctype IN ( ' + @sql_col
        + ') ) AS pvt 
	ORDER BY pvt.devid) devhis left join  (SELECT
						  devid, SUM(day) days, SUM(hour) hours FROM  #devstatehis group by devid 
						 ) info on info.devid = devhis.devid '
       PRINT ( @sql_str )
       EXEC (@sql_str)
  end

--4、执行存储过程（结果对比）

--原数据结果
SELECT * FROM  #devstatehis;<span style="font-family: Arial, Helvetica, sans-serif;"> </span>

--存储过程结果
EXEC [dbo].[dev_devstatehis2] '#devstatehis','devid','devsctype','hour',''
--结果对比图

--5、删除临时表
IF EXISTS (
	SELECT
		*
	FROM
		dbo.sysobjects
	WHERE
		xtype = 'U'
	AND Name = '#devstatehis'
)
BEGIN
	DROP TABLE #devstatehis ;
END

SQL行转列汇总

PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别(在数据库属性->选项->兼容级别改为   90 )

SQL2008 中可以直接使用

完整语法：

table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)

View Code

UNPIVOT 用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

完整语法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

 

典型实例

一、行转列

1、建立表格

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)

insert into tb VALUES ('张三','语文',74)
insert into tb VALUES ('张三','数学',83)
insert into tb VALUES ('张三','物理',93)
insert into tb VALUES ('李四','语文',74)
insert into tb VALUES ('李四','数学',84)
insert into tb VALUES ('李四','物理',94)

go

SELECT * FROM tb

go

姓名       课程       分数

---------- ---------- -----------

张三       语文        74

张三       数学        83

张三       物理        93

李四