Sql 行转列及列转行的使用

最新推荐文章于 2024-07-23 15:38:58 发布
最新推荐文章于 2024-07-23 15:38:58 发布
阅读量674 收藏 1
点赞数
分类专栏： SqlServer数据库 文章标签： sql server
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/afandaafandaafanda/article/details/41554861
版权
View Code 
 --行转列
 create table #CarLog(LogDate datetime,PathLine nvarchar(10),Amount int)
 --drop table #CarLog
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','1号线',1)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-25','1号线',91)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-26','1号线',66)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',44)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',33)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','5号线',12)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','6号线',22)
 select * from #CarLog

 --方法1
 SELECT LogDate,
 (CASE WHEN PathLine='1号线' THEN Amount ELSE 0 END)AS '1号线',
 (CASE WHEN PathLine='2号线' THEN Amount ELSE 0 END)AS '2号线',
 (CASE WHEN PathLine='3号线' THEN Amount ELSE 0 END)AS '3号线',
 (CASE WHEN PathLine='4号线' THEN Amount ELSE 0 END)AS '4号线',
 (CASE WHEN PathLine='5号线' THEN Amount ELSE 0 END)AS '5号线',
 (CASE WHEN PathLine='6号线' THEN Amount ELSE 0 END)AS '6号线'
 FROM #CarLog 
 Group By PathLine,LogDate,Amount

 --方法2
 declare @Str nvarchar(max)
 set @str='select [LogDate]'
 select @str=@str+',['+PathLine+']' from #CarLog group by PathLine
 --exec @str
 print @str
 set @str=@str+' FROM (
  SELECT LogDate,Amount,PathLine
  FROM #CarLog ) AS c
 PIVOT ( sum(Amount) FOR PathLine IN
   ('
 select @str=@str+'['+PathLine+'],' from #CarLog group by PathLine
 
 set @str=left(@str,Len(@str)-1)
 set @str=@str+ ')) AS thePivot
 ORDER BY LogDate'
 print @str
 
 declare @Table table(Logdate datetime,1号线 int, 3号线 int,4号线 int,5号线 int,6号线 int)
 INSERT INTO @Table exec(@str)
 SELECT * FROM @Table
 --drop table #T
 
 select [LogDate],[1号线],[3号线],[5号线],[6号线] FROM (
  SELECT LogDate,Amount,PathLine
  FROM #CarLog ) AS cl
 PIVOT ( sum(Amount) FOR PathLine IN
   ([1号线],[3号线],[5号线],[6号线])) AS thePivot
 ORDER BY LogDate
 ----------------------------------分割线-----------------------------------------------------
 CREATE  TABLE [#StudentScores]
 (
     [UserName]         NVARCHAR(20),        --学生姓名
     [Subject]          NVARCHAR(30),        --科目
     [Score]            FLOAT,               --成绩
 )
 
 INSERT INTO [#StudentScores] SELECT 'Nick', '语文', 81
 INSERT INTO [#StudentScores] SELECT 'Nick', '数学', 91
 INSERT INTO [#StudentScores] SELECT 'Nick', '英语', 72
 INSERT INTO [#StudentScores] SELECT 'Nick', '生物', 83
 INSERT INTO [#StudentScores] SELECT 'Kent', '语文', 84
 INSERT INTO [#StudentScores] SELECT 'Kent', '数学', 99
 INSERT INTO [#StudentScores] SELECT 'Kent', '英语', 77
 INSERT INTO [#StudentScores] SELECT 'Kent', '生物', 79
 --drop table #StudentScores
 ---------------------------
 select * from #StudentScores
 
 SELECT 
       UserName, 
       MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
       MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
       MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
       MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
 FROM dbo.[#StudentScores]
 GROUP BY UserName
 
 -----------------------------分割线----------------------------------------------------
 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-05-01', '支付宝', 50, 1
 INSERT INTO [#Inpours] SELECT '张三', '2010-06-14', '支付宝', 50, 1
 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
 
 select * from #Inpours
 
 SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
        CASE PayType WHEN '支付宝'      THEN SUM(Money) ELSE 0 END AS '支付宝',
        CASE PayType WHEN '手机短信'    THEN SUM(Money) ELSE 0 END AS '手机短信',
        CASE PayType WHEN '工商银行卡'  THEN SUM(Money) ELSE 0 END AS '工商银行卡',
        CASE PayType WHEN '建设银行卡'  THEN SUM(Money) ELSE 0 END AS '建设银行卡'
 FROM #Inpours
 GROUP BY CreateTime, PayType
 
 ------
 SELECT 
        CreateTime, 
        ISNULL(SUM([支付宝]), 0) AS [支付宝], 
        ISNULL(SUM([手机短信]), 0) AS [手机短信],
        ISNULL(SUM([工商银行卡]), 0) AS [工商银行卡], 
        ISNULL(SUM([建设银行卡]), 0) AS [建设银行卡]
 FROM
 (
     SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
            CASE PayType WHEN '支付宝'     THEN SUM(Money) ELSE 0 END AS '支付宝',
            CASE PayType WHEN '手机短信'   THEN SUM(Money) ELSE 0 END AS '手机短信',
            CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡',
            CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建设银行卡'
     FROM #Inpours
     GROUP BY CreateTime, PayType
 ) T
 GROUP BY CreateTime
 ------
 DECLARE @cmdText    VARCHAR(8000);
 DECLARE @tmpSql        VARCHAR(8000);
 
 SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,' + CHAR(10);
 print @cmdText
 SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' + PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType 
                 + ''',' + CHAR(10)  FROM (SELECT DISTINCT PayType FROM #Inpours ) T
 print @cmdText
 SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)
 
 SET @cmdText = @cmdText + ' FROM #Inpours     GROUP BY CreateTime, PayType ';
 print @cmdText
 SET @tmpSql ='SELECT CreateTime,' + CHAR(10);
 SELECT @tmpSql = @tmpSql + ' ISNULL(SUM(' + PayType  + '), 0) AS ''' + PayType  + ''','  + CHAR(10)
                     FROM  (SELECT DISTINCT PayType FROM #Inpours ) T
 print @tmpSql
 SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ' FROM (' + CHAR(10);
 print @tmpSql
 SET @cmdText = @tmpSql + @cmdText + ') T GROUP BY CreateTime ';
 PRINT @cmdText
 EXECUTE (@cmdText);
 -------
 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
 
 ----
 /*
 消息 325，级别 15，状态 1，第 9 行
 
 'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。
 这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。
 有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。 
 例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。
 **/
 
 
 ------------------------------------
 CREATE TABLE #ProgrectDetail
 (
     ProgrectName         NVARCHAR(20), --工程名称
     OverseaSupply        INT,          --海外供应商供给数量
     NativeSupply         INT,          --国内供应商供给数量
     SouthSupply          INT,          --南方供应商供给数量
     NorthSupply          INT           --北方供应商供给数量
 )
 --drop table #ProgrectDetail
 
 INSERT INTO #ProgrectDetail
 SELECT 'A', 100, 200, 50, 50
 UNION ALL
 SELECT 'B', 200, 300, 150, 150
 UNION ALL
 SELECT 'C', 159, 400, 20, 320
 UNION ALL
 SELECT 'D', 250, 30, 15, 15
 
 select * from #ProgrectDetail
 ------
 SELECT ProgrectName, 'OverseaSupply' AS Supplier,
         MAX(OverseaSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 UNION ALL
 SELECT ProgrectName, 'NativeSupply' AS Supplier,
         MAX(NativeSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 UNION ALL
 SELECT ProgrectName, 'SouthSupply' AS Supplier,
         MAX(SouthSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 UNION ALL
 SELECT ProgrectName, 'NorthSupply' AS Supplier,
         MAX(NorthSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 --------
 SELECT ProgrectName,Supplier,SupplyNum
 FROM 
 (
     SELECT ProgrectName, OverseaSupply, NativeSupply,
            SouthSupply, NorthSupply
      FROM #ProgrectDetail
 )T
 UNPIVOT 
 (
     SupplyNum FOR Supplier IN
     (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
 ) P
feng1456
关注
专栏目录
SQL 列转行
baidu_36414377的博客
03-05 4868
SQL 列转行函数 Oracle： listagg（） **listagg(字段名，’‘分隔符’) within group(order by 字段名) ** with temp as( select 'China' nation ,'Guangzhou' city from dual union all select 'China' nation ,'Shanghai' city from dual union all select 'China' nation ,'Beijin
DB2 SQL 实现行转列，列转行
01-29
DB2 SQL 通过函数(CONCAT/POSSTR/LOCATE)实现行转列，列转行 可以按照标点把多列转换为一行，多行转换为一列
sql列转行
威尔软件
03-21 76
CREATETABLE[Test]([id][int]IDENTITY(1,1)NOTNULL,[name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[subject][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[Source][numeric](18,0)NULL)ON[PRIMARY]GO INSERTIN...
sql列转行 行转列
最新发布
weixin_48485132的博客
07-23 5792
在 SQL 中，转换数据以按列排列的值成为按行排列的值（即所谓的“列转行”或“列转行”）是常见的数据操作需求。这个操作在不同的数据库管理系统中可以通过不同的技术手段来实现。在 SQL 中，行转列（也称为“透视”或“旋转”）的操作是将行数据转换为列数据，这在数据总结和分析中非常有用。不同的数据库系统有不同的方法来实现行转列的操作。操作的数据库，如 Oracle 和 SQL Server，可以直接使用。将多个列的数据合并到一个新的结果集的单一列中。如果你的列数是固定的，可以使用。函数可以实现类似的操作。
SQL行转列，列转行
weixin_34166847的博客
05-18 608
SQL 行转列，列转行 行列转换在做报表分析时还是经常会遇到的，今天就说一下如何实现行列转换吧。 行列转换就是如下图所示两种展示形式的互相转换 行转列 假如我们有下表： SELECT * FROM student PIVOT ( SUM(score) FOR subject IN (语文, 数学, 英语) ) 通过上面 SQL 语句即可得到下面的结果 PIVOT 后跟一个聚合函数来拿...
SQL列转行
Kerwin Ma 的博客
04-12 3418
SQL列转行就是获取数据表中的某一列数据整合成一条记录 函数名称：GROUP_CONCAT 用法：select GROUP_CONCAT(userid SEPARATOR ”) FROM detail assis_userid为要获取的列名，separator为分隔符，在引号里面可以设置以什么分隔，默认以 ，逗号分隔。 举例： 1、默认分割符 SQL： SELECT GROUP_CO...
SQL行转列和列转行代码详解
01-19
行列互转，可以分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。 --创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO CREATE ...
SQL行转列、列转行的简单实现
09-09
在SQL数据库操作中，行转列和列转行是数据处理和分析中常见的需求，尤其在报表制作和数据展示时。这两种转换可以帮助我们更有效地组织和呈现数据。本篇文章将详细探讨MySQL中如何实现行转列和列转行的操作。 **行转...
SQL行转列和列转行-静态互转-动态互转
03-09
有case when方式和2005之后的内置pivot和unpivot方法来实现，行列互转，可以分为静态互转，动态互转。
sql 列转行
hlvy
05-13 472
供参考: SELECT US.LOGONID,ENT.NAME,ENT.SAPID,T1.ID,T1.ORDER_ID, (CASE WHEN T2.REFUND_STATUS IS NOT NULL AND T2.REFUND_STATUS!= '' THEN DECODE(T2.REFUND_STATUS,'1','退款确认中','2','已退款','3','退款驳回') ELSE DE...
SQL 中的列转行
Paul Xin
07-22 1657
在工作中，经常遇到需要列转行的操作。比如：
SQL server列转行
baidu_37721054的博客
08-11 122
select ' ' +cast(cast( as decimal(18,1)) as varchar(20)) +'' + case when a.同比>= 0 THEN '+'+cast(cast(a.同比*100 as decimal(18,1)) as varchar(20)) when a.同比< 0 THEN cast(cast(a.同比*100 as decimal(18,1)) as varchar(20)) ...
SQL行转列与列转行实例解析：PIVOT vs. CASE-WHEN
在IT开发过程中，行转列和列转行操作是非常常见的数据转换需求。这两种操作的主要目的是将数据的结构从一维转换为二维，以便于分析和展示。在这个场景下，我们将讨论如何使用SQL语言中的CASE WHEN语句和SQL Server ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值