SQL Server 2005 T-SQL学习笔记:建立动态的PIVOT
来源:互联网 发布:mac上的word软件 编辑:程序博客网 时间:2024/06/13 21:27
利用CTE,我们可以方便地一列中的所有值连接成一个字串,然后在PIVOT语句中使用,以下是示例:
----Static PIVOT===========
select * from
(
select CustomerID, datepart(yyyy,OrderDate) as Years, 1 As Counts
from Sales.SalesOrderHeader
) piv PIVOT
(
sum(Counts)
for Years in ([2001],[2002],[2003])
) as chd
Order By CustomerID
--Dynamic PIVOT============
declare @Years as nvarchar(100);
with YearCTE as
(
select distinct year(OrderDate) as YearNum from Sales.SalesOrderHeader
)
select @Years = isnull(@Years + N',[', '[' ) + cast(YearNum as nvarchar(4)) + ']' from YearCTE order by YearNum;
print @Years;
declare @Sql as nvarchar(max);
set @Sql = N'select * from
(
select CustomerID, datepart(yyyy,OrderDate) as Years, 1 As Counts
from Sales.SalesOrderHeader
) piv PIVOT
(
sum(Counts)
for Years in (' + @Years + ')
) as chd
Order By CustomerID';
execute sp_executesql @sql;
select * from
(
select CustomerID, datepart(yyyy,OrderDate) as Years, 1 As Counts
from Sales.SalesOrderHeader
) piv PIVOT
(
sum(Counts)
for Years in ([2001],[2002],[2003])
) as chd
Order By CustomerID
--Dynamic PIVOT============
declare @Years as nvarchar(100);
with YearCTE as
(
select distinct year(OrderDate) as YearNum from Sales.SalesOrderHeader
)
select @Years = isnull(@Years + N',[', '[' ) + cast(YearNum as nvarchar(4)) + ']' from YearCTE order by YearNum;
print @Years;
declare @Sql as nvarchar(max);
set @Sql = N'select * from
(
select CustomerID, datepart(yyyy,OrderDate) as Years, 1 As Counts
from Sales.SalesOrderHeader
) piv PIVOT
(
sum(Counts)
for Years in (' + @Years + ')
) as chd
Order By CustomerID';
execute sp_executesql @sql;
- SQL Server 2005 T-SQL学习笔记:建立动态的PIVOT
- SQL Server 2005 T-SQL学习笔记:PIVOT和UNPIVOT
- SQL Server 2005 T-SQL学习笔记:TOP的增强
- SQL Server学习笔记之 T-SQL研究之[表结构的建立]
- sql server:sql动态行转列(pivot) 例子
- Sql Server的Pivot函数
- sql server 的T-SQL 学习笔记(一)
- sql server 的T-SQL 学习笔记(三)
- sql server 的T-SQL 学习笔记(四)
- sql server 的T-SQL 学习笔记(五)
- sql server 的T-SQL 学习笔记(六)
- sql server 的T-SQL 学习笔记(七)
- sql server 的T-SQL 学习笔记(八)
- sql server 的T-SQL 学习笔记(九)
- SQL Server 2005 T-SQL 学习笔记:排名函数
- SQL Server 2005 T-SQL 学习笔记:Try Catch
- SQL Server 2005 T-SQL学习笔记:CTE
- 【转】SQL Server 动态行转列(PIVOT)
- SQL Server 2005 T-SQL 学习笔记:排名函数
- zjut 1166 a-b
- SQL Server 2005 T-SQL 学习笔记:Try Catch
- 射影几何入门(连载八)- 对合(Involution)
- ASP.NET中调用非托管DLL的问题
- SQL Server 2005 T-SQL学习笔记:建立动态的PIVOT
- SQL Server 2005 T-SQL学习笔记:PIVOT和UNPIVOT
- 什么是数据库名、实例名、数据库域名、全局数据库名、服务名?
- SQL Server 2005 T-SQL学习笔记:CTE
- Hello! Flex 4
- 熬夜人必看
- SQL Server 2005 T-SQL学习笔记:TOP的增强
- HTMLParser使用详解(1)- 初始化Parser
- 使用BinaryFormatter进行序列化