SQL cursor in dynamic content

Question

SQL cursor in dynamic content

I need a little help wrapping my head around the CURSOR.

I made an aspx report, it was pretty simple. For each project, I needed to see how many hours passed in research, development and internal. This first one was pretty simple, I just hardcoded a solution with 3 groups and equal 3 time types. The boss thought it was a brilliant report, so he wanted it to cover all clients. Unfortunately, the next client had 5 groups and each group had 2-7 different types of time for each.

Now I need a dynamic solution where I can specify the groups (done) and specify what types of times go to each group (completed), lastly I need to concatenate these two with the table in which the hours are recorded.

I have a main cursor where I can list projects, type of time and how much time was spent during that time group.

The problem is that I need the report Group_Header 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

+3

sql-server dynamic sql-server-2005 cursor

DoStuffZ 21 jan. 13 at 13:35

source to share

1 answer

Arion · Accepted Answer · 2013-01-21T13:53:33+0000

I'm not sure about the column names. So in this example, I expect tbl_ProjectTransaction

to have a column named projectName

and tbl_TimeTypeGrouping

with GroupName

. Like someone who entered the question, you shouldn't use a cursor in this case. You must use a dynamic bar. Here's an example:

Get the names of such groups:

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

This will give you:

'[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)

Literature:

Using PIVOT and UNPIVOT

SQL cursor in dynamic content

More articles: