SQL行转列和列转行代码详解

前端之家收集整理的这篇文章主要介绍了SQL行转列和列转行代码详解前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。
在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再总结一次。
行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列)。

sql;"> --创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders ( orderid int NOT NULL PRIMARY KEY NONCLUSTERED,orderdate datetime NOT NULL,empid int NOT NULL,custid varchar(5) NOT NULL,qty int NOT NULL ); CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid ON dbo.Orders(orderdate,orderid); INSERT INTO dbo.Orders(orderid,orderdate,empid,custid,qty) VALUES(30001,'20020802',3,'A',10); INSERT INTO dbo.Orders(orderid,qty) VALUES(10001,'20021224',1,12); INSERT INTO dbo.Orders(orderid,qty) VALUES(10005,'B',20); INSERT INTO dbo.Orders(orderid,qty) VALUES(40001,'20030109',4,40); INSERT INTO dbo.Orders(orderid,qty) VALUES(10006,'20030118','C',14); INSERT INTO dbo.Orders(orderid,qty) VALUES(20001,'20030212',2,qty) VALUES(40005,'20040212',qty) VALUES(20002,'20040216',qty) VALUES(30003,'20040418',15); INSERT INTO dbo.Orders(orderid,qty) VALUES(30004,'20020418',22); INSERT INTO dbo.Orders(orderid,qty) VALUES(30007,'20020907','D',30); GO

行转列-静态方案:

sql;"> --行转列的静态方案一:CASE WHEN,兼容sql2000 select custid,sum(case when YEAR(orderdate)=2002 then qty end) as [2002],sum(case when YEAR(orderdate)=2003 then qty end) as [2003],sum(case when YEAR(orderdate)=2004 then qty end) as [2004] from orders group by custid; GO --行转列的静态方案二:PIVOT,sql2005及以后版本 select * from (select custid,YEAR(orderdate) as years,qty from orders) as ord pivot(sum(qty) for years in([2002],[2003],[2004]))as p GO

行转列-动态方案:加入了xml处理和sql注入预防判断

@Y); END IF dbo.fn_ChecksqlInjection(@sql)=0 SET @sql=N'SELECT custid'+@sql+N' FROM orders group by custid' PRINT @sql EXEC sp_executesql @sql GO --行转列的动态方案二:PIVOT,sql2005及以后版本 DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY); INSERT INTO @T SELECT DISTINCT YEAR(orderdate) from orders; DECLARE @Y INT; SET @Y=(SELECT MIN(years) from @T); DECLARE @sql NVARCHAR(4000)=N''; --这里使用了xml处理来处理类组字符串 SET @sql=STUFF((SELECT N','+QUOTENAME(years) FROM @T FOR XML PATH('')),N''); IF dbo.fn_ChecksqlInjection(@sql)=0 SET @sql=N'select * from (select DISTINCT custid,qty from orders) as ord pivot(sum(qty) for years in('+@sql+N'))as p'; PRINT @sql; EXEC SP_EXECUTEsql @sql; GO

列转行:

1 AND TABLE_NAME='PvtCustOrders' FOR XML PATH('')),N'') SET @sql=N'SELECT custid,qty from dbo.pvtCustOrders unpivot(qty for years in('+@sql+'))as up'; PRINT @sql; EXEC SP_EXECUTEsql @sql;

总结

以上就是本文关于sql行转列和列转行代码详解的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:MysqL查询和嵌套查询优化实例解析等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对编程之家网站的支持

猜你在找的MsSQL相关文章