SQL 行转列

jopen 9年前

先建立一张临时表：
CREATE TABLE #Inpours
(
   [ID]                INT IDENTITY(1,1), 
   [UserName]          NVARCHAR(20),  --游戏玩家
    [CreateTime]        DATETIME,      --充值时间    
    [PayType]           NVARCHAR(20),  --充值类型    
    [Money]             DECIMAL,       --充值金额
    [IsSuccess]         BIT,           --是否成功 1表示成功， 0表示失败
    CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID)

)

添加数据：

INSERT INTO #Inpours SELECT '张三', '2010-06-14', '手机短信', 100, 1
INSERT INTO #Inpours SELECT '李四', '2010-06-14', '手机短信', 100, 1
INSERT INTO #Inpours SELECT '李四', '2010-07-14', '支付宝', 100, 1
INSERT INTO #Inpours SELECT '王五', '2010-07-14', '工商银行卡', 100, 1
INSERT INTO #Inpours SELECT '赵六', '2010-07-14', '建设银行卡', 100, 1
INSERT INTO #Inpours SELECT '王五1', '2010-07-22', '招商银行', 200, 1
INSERT INTO #Inpours SELECT '赵六1', '2010-07-25', '中国邮政储蓄', 10, 1

 

按支付方式转行：

（1）

DECLARE @cmdText    VARCHAR(8000); 
DECLARE @tmpSql        VARCHAR(8000); 


SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,' + CHAR(10); 
SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' + PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType  
                + ''',' + CHAR(10)  FROM (SELECT DISTINCT PayType FROM #Inpours ) T 
SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1) 
SET @cmdText = @cmdText + ' FROM #Inpours     GROUP BY CreateTime, PayType '; 
SET @tmpSql ='SELECT CreateTime,' + CHAR(10); 
SELECT @tmpSql = @tmpSql + ' ISNULL(SUM(' + PayType  + '), 0) AS ''' + PayType  + ''','  + CHAR(10) 
                    FROM  (SELECT DISTINCT PayType FROM #Inpours ) T 
SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ' FROM (' + CHAR(10); 
SET @cmdText = @tmpSql + @cmdText + ') T GROUP BY CreateTime '; 
PRINT @cmdText 
EXECUTE (@cmdText);

（2）

有时可能会出现这样的错误：消息 325，级别 15，状态 1，第 9 行

'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。

这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。 例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称

EXEC sp_dbcmptlevel database, 90


SELECT  
        CreateTime, [支付宝] , [手机短信], 
        [工商银行卡] , [建设银行卡] ,[中国邮政储蓄],[招商银行]
FROM 
( 
    SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money 
    FROM #Inpours 
) P 
PIVOT ( 
            SUM(Money) 
            FOR PayType IN 
            ([支付宝], [手机短信], [工商银行卡], [建设银行卡],[中国邮政储蓄],[招商银行]) 
      ) AS T 
ORDER BY CreateTime

按人员转行：

declare @cmdText varchar(8000)
 set @cmdText='select [UserName], '
 select @cmdText=@cmdText+' sum(case payType when'''+payType+'''Then money else 0 end) as '''+payType
 +''','+char(10) from (select Distinct payType from #Inpours) T
 print @cmdText--发现多一个逗号下面把逗号去掉
 set @cmdText=left(@cmdText,len(@cmdText)-2)--去掉逗号
 set @cmdText=@cmdText+'from #Inpours group by userName'
 print @cmdText
 exec(@cmdText)