SQL Server中的数据透视表 [英] Pivot data in SQL Server

问题描述

我需要使用SQL Server 2008R2存储过程创建一个报告，该报告将返回以下形状的数据:

I need to create a report using SQL Server 2008R2 Stored procedure that will return the following shape of data:

我在SQL Server中的视图正在返回这样的数据:

My view in SQL Server is returning data like this:

用户将选择开始日期和结束日期，并且该日期范围将显示在列中，即，如果用户选择2012年4月1日至2012年4月7日，则选择7天，则第1天至第7天将显示在列中.左列将显示"ApplicationId"字段的总数. 请注意，已分配"，正在处理"，..是状态"字段.

The user will select start_date and end_date and that date range will be displayed in columns, i.e. if user selects Apr 1,2012 to Apr 7, 2012 then 7 days, Day1 to Day7 will be shown in columns. The left column will show the totals of "ApplicationId" field. Note that "Assigned", "Processing",.. are Status field.

推荐答案

检查此示例.您可以创建动态列/动态数据透视表.但您必须在sql server中使用execute.

Check this example.. as like i have implemented using this one. you can create your dynamic columns/ dynamic pivot. but you have to it using execute in sql server.

您可以根据需要实施/修改查询.

You can implement/ modify your query as you want.

引用:具有动态数据透视的SQL Server 2005中的列

DECLARE @query NVARCHAR(4000) SET @query = N'SELECT tID, '+ @cols +' FROM (SELECT  t2.tID
      , t1.ColName
      , t2.Txt FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p PIVOT ( MAX([Txt]) FOR ColName IN ( '+ @cols +' ) ) AS pvt ORDER BY tID;'

Executing this with

EXECUTE(@query)

有关此的更多参考:
SQL Server-动态PIVOT表-SQL注入
动态PIVOT查询交叉表结果
SQL Server中的动态透视-其中包含一些示例有点符合您的要求.

For more reference about this:
SQL Server - Dynamic PIVOT Table - SQL Injection
Dynamic PIVOT Query For Cross Tab Result
Dynamic Pivoting in SQL Server - it contains some example that somewhat match your requirement..

希望这项帮助.

这篇关于SQL Server中的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！