如何在SQL Server 2005 存储过程中使用PIVOT 加入两个视图 [英] How to use PIVOT in SQL Server 2005 Stored Procedure Joining Two Views

问题描述

早上好，

我有 2 个视图:ICCUDays 包含每个帐户的一条记录，其中包含字段 ACCOUNT 和 ICCUDays，ICCUEnctrSelectedRevCatsDirCost 包含每个帐户的多条记录，包含字段 ACCOUNT、UBCATEGORY 和 DirectCost.

I have 2 views: ICCUDays which contains one record per account with fields ACCOUNT and ICCUDays, ICCUEnctrSelectedRevCatsDirCost which contains multiple records per account with fields ACCOUNT, UBCATEGORY, and DirectCost.

我的目标:创建一个存储过程，通过 UBCATEGORY 使用 ICCUDays 和 DirectCost 为每个 ACCOUNT 输出一条记录.这将是一个交叉表或数据透视表，并且必须允许在一个或多个直接成本子类别桶中出现空值的可能性.最后，需要将此交叉表或数据透视表发送到新表 EnctrUBCatPivot.

My Goal: To create a stored procedure that outputs one record per ACCOUNT with ICCUDays and DirectCost by UBCATEGORY. This will be a crosstab or pivot and has to allow for the possibility of nulls in one or more direct cost ubcategory bucket. Finally, this crosstab or pivot needs to be sent to a new table EnctrUBCatPivot.

问题:上述场景的正确 PIVOT 语法是什么?鉴于我想为许多 UBCATEGORY 条目输出直接成本，我如何编写 TSQL 来迭代这些条目并按帐户和 UBCATEGORY 进行透视?所有这些是在一个 sproc 中完成的，还是必须分成多个 sproc 才能将结果写入表格?

Questions: What is the correct PIVOT syntax for the above scenario? Given that I want to ouptut direct cost for however many UBCATEGORY entries, how do I write the TSQL to iterate over these and pivot by account and UBCATEGORY? Is all this accomplished in one sproc, or does it have to be separated into multiple sprocs to write the results out to a table?

这是我目前编写的代码:

Here's the code I've written so far:

ALTER PROCEDURE [dbo].[spICCUMain]
-- Add the parameters for the stored procedure here

AS
declare @columns varchar(8000)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @columns = COALESCE(@columns + ',[' + cast(UBCATEGORYmid as varchar) + ']','[' + cast(UBCATEGORYmid as varchar)+ ']')
FROM vwICCUEnctrSelectedRevCatsDirCost
GROUP BY UBCATEGORYmid


DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM vwICCUEnctrSelectedRevCatsDirCost
PIVOT
(
MAX(DirectCost)
FOR [UBCATEGORYmid]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

END

这很有效，因为它输出每个 UBCATEGORY 的帐户和所有直接成本.但是，我需要在 ACCOUNT 上内部连接到 vwICCUDAYS，以将一列添加到 ICCUDays 的数据透视表中.每个 UBCATEGORYmid 的最终数据透视列应该是 Account、ICCUDays、Direct Cost.

This works fine in that it outputs Account and all the Direct Costs for each UBCATEGORY. However, I need to inner join to vwICCUDAYS on ACCOUNT to add a column to the pivot for ICCUDays. Final pivot columns should be Account, ICCUDays, Direct Cost for each UBCATEGORYmid.

我对合并语法不是很熟悉，因此无法辨别如何修改它以添加更多列，也不确定如何/在何处添加内部联接语法以添加 ICCUDays.

I'm not very familiar with the coalesce syntax and thus cannot discern how to modify it to add further columns, nor am I sure how/where to add the inner join syntax to add ICCUDays.

有人能指出我正确的方向吗?谢谢，希德

Can someone point me in the proper direction? Thanks, Sid

推荐答案

您需要知道 PIVOT 的所有可能值.因此，除非您使用动态 SQL，否则很难直接使用 T-SQL 执行此操作，并且这会很快变得棘手.最好将所有行传递回表示层或报告编写器，并让它将它们转向一边.

You need to know all of the possible values to PIVOT by. So it is difficult to do this with T-SQL directly unless you use dynamic SQL and this can get hairy pretty quickly. Probably better to pass all of the rows back to the presentation tier or report writer and let it turn them sideways.

如果您事先知道所有 UBCategory 值，这里是一个快速 PIVOT 示例.我遗漏了 ICCUDays，因为它似乎无关紧要，除非有来自该视图的列作为结果的一部分.

Here is a quick PIVOT example if you know all of the UBCategory values in advance. I left out ICCUDays since it seems rather irrelevant unless there are columns that come from that view as part of the result.

USE tempdb;
GO
SET NOCOUNT ON;
GO

-- who on earth is responsible for your naming scheme?
CREATE TABLE dbo.ICCUEnctrSelectedRevCatsDirCost
(
    Account INT,
    UBCategory VARCHAR(10),
    DirectCost DECIMAL(9,2)
);

INSERT dbo.ICCUEnctrSelectedRevCatsDirCost
    SELECT 1, 'foo', 5.25
    UNION SELECT 1, 'bar', 6.25
    UNION SELECT 1, 'smudge', 8.50
    UNION SELECT 2, 'foo', 9.25
    UNION SELECT 2, 'brap', 2.75;

SELECT Account,[foo],[bar],[smudge],[brap] FROM 
    dbo.ICCUEnctrSelectedRevCatsDirCost
    -- WHERE <something>, I assume ???
PIVOT
(
    MAX(DirectCost)
    FOR UBCategory IN ([foo],[bar],[smudge],[brap])
) AS p;

GO
DROP TABLE dbo.ICCUEnctrSelectedRevCatsDirCost;

要使其更具动态性，您必须获取 DISTINCT UBCategory 值的逗号分隔列表，并动态构建枢轴.所以它可能看起来像这样:

To make this more dynamic, you'd have to get the comma separated list of DISTINCT UBCategory values, and build the pivot on the fly. So it might look like this:

USE tempdb;
GO
SET NOCOUNT ON;
GO

-- who on earth is responsible for your naming scheme?
CREATE TABLE dbo.ICCUEnctrSelectedRevCatsDirCost
(
    Account INT,
    UBCategory VARCHAR(10),
    DirectCost DECIMAL(9,2)
);

INSERT dbo.ICCUEnctrSelectedRevCatsDirCost
    SELECT 1, 'foo', 5.25
    UNION SELECT 1, 'bar', 6.25
    UNION SELECT 1, 'smudge', 8.50
    UNION SELECT 2, 'foo', 9.25
    UNION SELECT 2, 'brap', 2.75
    UNION SELECT 3, 'bingo', 4.00;

DECLARE @sql NVARCHAR(MAX),
    @col NVARCHAR(MAX);

SELECT @col = COALESCE(@col, '') + QUOTENAME(UBCategory) + ','
    FROM 
    (
        SELECT DISTINCT UBCategory
        FROM dbo.ICCUEnctrSelectedRevCatsDirCost
    ) AS x;

SET @col = LEFT(@col, LEN(@col)-1);

SET @sql = N'SELECT Account, $col$ FROM 
    dbo.ICCUEnctrSelectedRevCatsDirCost
    -- WHERE <something>, I assume ???
PIVOT
(
    MAX(DirectCost)
    FOR UBCategory IN ($col$)
) AS p;';

SET @sql = REPLACE(@sql, '$col$', @col);

--EXEC sp_executeSQL @sql;
PRINT @sql;

GO
DROP TABLE dbo.ICCUEnctrSelectedRevCatsDirCost;

然后要将数据发送到新表"，您可以将查询设为 INSERT INTO ... SELECT 而不是直接的 SELECT.当然，这似乎没什么用，因为为了编写该插入语句，您需要知道列的顺序(这种方法不能保证)并且您需要已经为每个潜在的 UBCategory 放入列无论如何都值得，所以这看起来很鸡和蛋.

Then to "send the data to a new table" you can just make the query an INSERT INTO ... SELECT instead of a straight SELECT. Of course, this seems kind of useless, because in order to write that insert statement, you need to know the order of the columns (which isn't guaranteed with this approach) and you need to have already put in columns for each potential UBCategory value anyway, so this seems very chicken and egg.

这篇关于如何在SQL Server 2005 存储过程中使用PIVOT 加入两个视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！