SQL动态内容中的游标 [英] SQL Cursor in dynamic content

问题描述

我需要一点帮助，围绕CURSOR包装我的头。

I need a little help wrapping my head around CURSOR.

我做了一个aspx报告，很简单。对于每个项目，我需要看看有多少个小时去研究，发展和内部。第一个是相当简单，我刚刚编码解决方案与3组和等于3种类型的时间。 Boss认为这是一个辉煌的报告，所以他想要覆盖所有客户。不幸的是，下一个客户有5个组，每个组有2到7种不同类型的时间。

I made an aspx report, it was quite simple. For each project I needed to see how many hours went on Research, Development and Internal. That first one was quite simple, I just hardcoded the solution with 3 groups and an equal 3 types of time. Boss thought that was a brilliant report so he wanted it to cover all customers. Unfortunately the next customer had 5 groups and each group had 2-7 different types of time to each.

现在我需要一个动态解决方案， ）和指定哪些类型的时间进入每个组（完成），最后我需要结合这两个表，记录花费的时间。

Now I need a dynamic solution where I can specify the groups (done) and specify which types of time goes into each group (done), last I need to combine those two with the table that record hours spent.

我有一个基本光标在这里我可以列出项目，时间类型和在这一组时间内花费了多少时间。

I have a basic cursor where I can list the projects, the type of time and how much time was spent within this group of time.

我有什么：

项目A - | - A组 - | - 5

项目B - | - A组 - | - 2

项目C - | - A组 - | - 10

What I have is:
Project A -|- Group A -|- 5
Project B -|- Group A -|- 2
Project C -|- Group A -|- 10

项目A - | - B组 - | - 1

项目B - | - B组 - | - 10

项目C - | - B组 - | - 2

Project A -|- Group B -|- 1
Project B -|- Group B -|- 10
Project C -|- Group B -|- 2

项目A - | - C组 - | - 0

项目B - | - | - 3

项目C - | - C组 - | - 7

Project A -|- Group C -|- 0
Project B -|- Group C -|- 3
Project C -|- Group C -|- 7

问题是，我需要报告为

Header____Group A | B组| C组| N组

项目A - | - 5 - | - 1 - | - 0 - | - x

项目B - | - 2 - | - 10 - | - 3 - | - y

项目C - | - 10 - | - 2 - | - 7 - | - z

Problem is, I need the report to be
Header____Group A|Group B|Group C|Group N
Project A -|- 5 -|- 1 -|- 0 -|- x
Project B -|- 2 -|- 10 -|- 3 -|- y
Project C -|- 10 -|- 2 -|- 7 -|- z

DECLARE @iTimeTypeGroupID int

DECLARE cur CURSOR LOCAL READ_ONLY
FOR 
SELECT iRefTimeTypeGroupID
FROM tbl_TimeTypeGrouping
WHERE iRefCustomerID = @customerID

OPEN cur

FETCH NEXT FROM cur
INTO @iTimeTypeGroupID

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT PT.iRefProjectID
        , PT.iRefTimeTypeID
        , SUM(PT.decNumberOfHours) sumNumberOfHours
    FROM tbl_ProjectTransaction PT
    WHERE iRefTimeTypeID IN (
        SELECT iRefTimeTypeID 
        FROM tbl_TimeTypeGrouping 
        WHERE iRefTimeTypeGroupID = @iTimeTypeGroupID
            AND iRefCustomerID = @customerID)
    GROUP BY PT.iRefProjectID
        , PT.iRefTimeTypeID

    FETCH NEXT FROM cur
    INTO @iTimeTypeGroupID
END

CLOSE cur
DEALLOCATE cur

推荐答案

我不确定列名。所以在这个例子中，我希望 tbl_ProjectTransaction 有一个 projectName 和 tbl_TimeTypeGrouping 拥有 GroupName 。像某人在问题中被抛出，在这种情况下你不应该使用光标。您应该使用动态轴。这里是和示例：

I am not sure about the column names. So in this example I expect the tbl_ProjectTransaction to have a column called projectName and tbl_TimeTypeGrouping to have a GroupName. Like someone commeted in the question you should not use a cursor in this case. You should use a dynamic pivot. Here is and example:

获取组的名称，如下所示：

Get the names of the groups like this:

DECLARE @cols VARCHAR(MAX)
SELECT @cols=STUFF
(
    (
        SELECT 
            ',' +QUOTENAME(tbl_TimeTypeGrouping.sGroupName) -- ????
        FROM
            tbl_TimeTypeGrouping
        FOR XML PATH('')
    )
,1,1,'')

这会给你：

'[Group A],[Group B],[Group C],[Group N]'

然后像这样做一个动态的枢纽：

And then do a dynamic pivot like this:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl_ProjectTransaction.sProjectName, -- ????
        tbl_ProjectTransaction.decNumberOfHours,
        tbl_TimeTypeGrouping.sGroupName -- ???
    FROM
        tbl_ProjectTransaction
        JOIN tbl_TimeTypeGrouping
            ON tbl_ProjectTransaction.iRefTimeTypeID=tbl_TimeTypeGrouping.iRefTimeTypeID
) AS SourceTable
PIVOT
(
    SUM(decNumberOfHours)
    FOR GroupName IN ('+@cols+')
) As Pvt'
EXECUTE(@query)

参考文献：

• 使用PIVOT和UNPIVOT

• Using PIVOT and UNPIVOT

这篇关于SQL动态内容中的游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！