获取 ROWS 作为 COLUMNS(SQL Server 动态 PIVOT 查询)
我使用的是 MS SQL 2008 R2，有三个具有以下架构的表:
I'm using MS SQL 2008 R2, have three tables with following schema:
表 1:包含每个工人的轮班信息
Table 1: Contains workshift info for each worker
CREATE TABLE workshift (
[ws_id] [bigint] NOT NULL,
[start_date] [datetime] NOT NULL,
[end_date] [datetime] NOT NULL,
[worker_id] [bigint] NOT NULL
)
INSERT INTO workshift VALUES (1, '2012-08-20 08:30:00', '2012-08-20 14:30:00', 1)
INSERT INTO workshift VALUES (2, '2012-08-20 14:30:00', '2012-08-20 22:30:00', 2)
表 2:包含货币面额
CREATE TABLE currency_denom (
[cd_id] [decimal](7, 2) NOT NULL,
[name] [nchar](100) NOT NULL
)
INSERT INTO currency_denom VALUES (1, '100.00')
INSERT INTO currency_denom VALUES (2, '50.00')
INSERT INTO currency_denom VALUES (3, '20.00')
INSERT INTO currency_denom VALUES (4, '10.00')
INSERT INTO currency_denom VALUES (5, '5.00')
INSERT INTO currency_denom VALUES (6, '1.00')
表 3:包含工人在每个班次中收到的每种面额的数量
Table 3: Contains the quantity of each denomination the worker has received in every workshift
CREATE TABLE currency_by_workshift (
[cd_id] [decimal](7, 2) NOT NULL,
[ws_id] [bigint] NOT NULL,
[qty] [int] NOT NULL
)
INSERT INTO currency_by_workshift VALUES (1, 1, 1)
INSERT INTO currency_by_workshift VALUES (2, 1, 2)
INSERT INTO currency_by_workshift VALUES (3, 1, 2)
INSERT INTO currency_by_workshift VALUES (2, 2, 3)
INSERT INTO currency_by_workshift VALUES (4, 2, 4)
INSERT INTO currency_by_workshift VALUES (5, 2, 2)
我需要获取列而不是行中的currency_by_workshift 值以及轮班值，即:
I need to get the currency_by_workshift values in columns instead of rows, along with the workshift values, that is:
workshift | workshift | workshift | 100.00 | 50.00 | 20.00 | 10.00 | 5.00 | 1.00
ws_id | start_date | end_date | | | | | |
1 | 2012-08-20 08:30:00 | 2012-08-20 14:30:00 | 1 | 2 | 2 | 0 | 0 | 0
2 | 2012-08-20 14:30:00 | 2012-08-20 22:30:00 | 0 | 2 | 0 | 4 | 2 | 0
我无法使用 case 来计算每种货币面额的数量，因为它们是可配置的，如果添加了新面额，则应修改查询.如果使用 PIVOT 功能也一样，还是我错了?
I'm not able to use a case to count quantities for each currency denomination because they are configurable, if a new denomination is added, the query should be modified. Same applies if using PIVOT function, or I'm wrong?
我怎样才能以这种方式获取信息?
How can I get the info that way?
推荐答案
您尝试执行的操作称为 PIVOT.有两种方法可以做到这一点，使用静态枢轴或动态枢轴.
What you are trying to do is called a PIVOT. There are two ways to do this, either with a Static Pivot or a Dynamic Pivot.
Static Pivot - 是您将行的值硬编码以转换为列的地方(参见 SQL Fiddle with Demo):
Static Pivot - is where you will hard-code the values of the rows to transform to columns (See SQL Fiddle with Demo):
select ws_id,
start_date,
end_date,
IsNull([100.00], 0) [100.00],
IsNull([50.00], 0) [50.00],
IsNull([20.00], 0) [20.00],
IsNull([10.00], 0) [10.00],
IsNull([5.00], 0) [5.00],
IsNull([1.00], 0) [1.00]
from
(
select ws.ws_id,
ws.start_date,
ws.end_date,
cd.name,
cbw.qty
from workshift ws
left join currency_by_workshift cbw
on ws.ws_id = cbw.ws_id
left join currency_denom cd
on cbw.cd_id = cd.cd_id
) x
pivot
(
sum(qty)
for name in ([100.00], [50.00], [20.00], [10.00], [5.00], [1.00])
) p
动态数据透视是在运行时确定列的地方(参见 SQL Fiddle with Demo):
Dynamic pivot is where the columns are determined at run-time (see SQL Fiddle with Demo):
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot AS NVARCHAR(MAX)
select @colsPivot =
STUFF((SELECT ', IsNull(' + QUOTENAME(rtrim(name)) +', 0) as ['+ rtrim(name)+']'
from currency_denom
GROUP BY name
ORDER BY cast(name as decimal(10, 2)) desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(name)
from currency_denom
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT ws_id, start_date, end_date,' + @colsPivot + ' from
(
select ws.ws_id,
ws.start_date,
ws.end_date,
cd.name,
cbw.qty
from workshift ws
left join currency_by_workshift cbw
on ws.ws_id = cbw.ws_id
left join currency_denom cd
on cbw.cd_id = cd.cd_id
) x
pivot
(
sum(qty)
for name in (' + @cols + ')
) p '
execute(@query)
两个版本都会产生相同的结果.
Both versions will produce the same results.
相关文章