Denormalizing....

Hal Levy · May 20th, 2005, 03:26 PM

Table 1

RID Name
---------------
1 Bob
2 Tom
3 Sally
4 Fran

Table2

RID Plan Percent
--------------------
3 1 13
2 4 10
3 2 20
1 5 15
3 4 5
3 5 28

I need to de-normalize this in my result set... Ordered from Largest to smallest Percent. So for RID=3 I would want back

RID Plan1 Pct1 Plan2 Pct2 Plan3 Pct3
-----------------------------------------------
3 2 30 5 28 1 13

My first thought was a temp table and doing something like this:

Code:

insert into #Temp  (Plan01, Pct01)
    (SELECT TOP 1 PlanID, PctShare
        from hcpmhcplan 
        where RID = 10100001
        order by PctShare desc, PlanID desc
        )

insert into #Temp  (Plan02, Pct02)
    (SELECT TOP 1 PlanID, PctShare
        from hcpmhcplan 
        where RID = 10100001 and PlanID not in (
            select PlanID from #Temp where RID = 10100001)
        order by PctShare desc, PlanID desc
        ) where RID = 10100001

insert into #Temp  (Plan03, Pct03)
    (SELECT TOP 1 PlanID, PctShare
        from hcpmhcplan 
        where RID = 10100001 and PlanID not in (
            Select PlanID from #Temp where RID = 10100001)
        order by PctShare desc, PlanID desc
        ) where RID = 10100001

Unfortunately- the order by doesn't seem to work in this case- SQL Server doesn't like it... I know all the nesting and in's are sloppy.. I want to get it to work before I worked on cleaning it up...

Anyone have any ideas?

Hal Levy
I am here to help you, not do it for you.
That is, unless you hire me. I am looking for work.

digby_dog · May 30th, 2005, 09:48 AM

Try the stored procedure below as an example of a possible solution.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[SP_Report] AS

Declare @Plan as Int
Declare @Perc as Int
Declare @Rid as int
Declare @Message as Varchar(255)
Declare @LastRid as Int
-- Declare the cursor ensuring the records are in the right order
DECLARE Report Insensitive CURSOR For
SELECT Rid, PlanP, Perc From RidPlanPerc
Order by Rid, Perc desc

OPEN Report

FETCH NEXT FROM Report INTO @Rid, @Plan, @Perc

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Check to see if there has been a change of Rid
    IF @LastRid <> @Rid
    BEGIN
        -- On change of Rid write out what we have build up so far
        SELECT @Message
        -- Blank out the message buffer
        SELECT @Message = ''
        -- Set the new Rid flag
        SELECT @LastRid = @Rid
        -- Process the first new Rid message
        SELECT @Message = CONVERT(varchar(5), @Rid) + ', ' + CONVERT(varchar(5), @Plan) + ', ' + CONVERT(varchar(5), @Perc)
    END
    ELSE
    -- No change of rid so add to the Message buffer
    BEGIN
        SELECT @Message = @Message + ' ' + CONVERT(varchar(5), @Plan) + ', ' + CONVERT(varchar(5), @Perc)

    END
    FETCH NEXT FROM Report INTO @Rid, @Plan, @Perc
END

-- processed all messages and write out any remaining content
SELECT @Message

CLOSE Report

DEALLOCATE Report

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO