0

I am Using SQLServer2008.

Below is my Stored Procedure

DECLARE @planTable TABLE
(
  Year VARCHAR(20) ,
  PlanTypeId INT
)

INSERT  INTO @planTable
    ( Year ,
      PlanTypeId 
    )
    ( SELECT DISTINCT
                pm.Year ,
                ptm.PlanTypeId
      FROM      dbo.PlanMaster AS pm
                INNER JOIN dbo.PlanTypeMaster AS ptm ON pm.PlanTypeId = ptm.PlanTypeId
    )

DECLARE @tmp TABLE
(
  BrokerCode VARCHAR(20) ,
  Year VARCHAR(20) ,
  PlanType VARCHAR(20) ,
  Amount DECIMAL(18, 2)
)

DECLARE @Year VARCHAR(20)
DECLARE @PlanTypeId INT
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT pt.Year,pt.PlanTypeId FROM @planTable AS pt
OPEN c1


FETCH NEXT FROM c1
INTO  @Year,@PlanTypeId

WHILE @@FETCH_STATUS = 0 
BEGIN

    INSERT  INTO @tmp
            ( BrokerCode ,
              Year ,
              PlanType ,
              Amount
            )
            ( SELECT    ( SELECT    bm.BrokerCode
                          FROM      dbo.BrokerMaster AS bm
                          WHERE     BrokerId = 30
                        ) ,
                        @Year ,
                        ( CASE WHEN ( SELECT    ptm.IsSingleInstallment
                                      FROM      dbo.PlanTypeMaster AS ptm
                                      WHERE     ptm.PlanTypeId = @PlanTypeId
                                    ) = 'true' THEN 'Single'
                               WHEN ( SELECT    ptm.IsSingleInstallment
                                      FROM      dbo.PlanTypeMaster AS ptm
                                      WHERE     ptm.PlanTypeId = @PlanTypeId
                                    ) = 'false' THEN 'Multiple'
                          END ) ,
                        ISNULL(( SUM(SelfAmount) + SUM(UnitAmount) ), 0)
              FROM      dbo.MemberBusiness AS mb
                        INNER JOIN dbo.PlanMaster AS pm ON mb.PlanId = pm.PlanId
                        INNER JOIN dbo.PlanTypeMaster AS ptm2 ON pm.PlanTypeId =    ptm2.PlanTypeId
              WHERE     mb.BrokerId = 30
                        AND pm.Year = @Year
                        AND ptm2.PlanTypeId = @PlanTypeId
            )


    FETCH NEXT FROM c1
INTO  @Year,@PlanTypeId

END
CLOSE c1
DEALLOCATE c1

SELECT  *
FROM    @tmp

And result of this stored procedure is something like this

----------------------------------------------------
  BrokerCode  |   Year  |   PlanType  |  Amount    |
----------------------------------------------------
    102       |    1    |   Single    |   100      |
----------------------------------------------------
    102       |    2    |   Single    |   200      |
----------------------------------------------------
    102       |    3    |   Single    |   300      |
----------------------------------------------------
    102       |    1    |   Multiple  |   100      |
----------------------------------------------------
    102       |    2    |   Multiple  |   200      |
----------------------------------------------------
    102       |    3    |   Multiple  |   300      |

Now I want to result like this..

------------------------------------------------------------------------------------
                          Single             |         Multiple                    |
-------------------------------------------------------------------------------------
BrokerCode |     1    |     2    |     3     |     1      |     2      |     3     |  
------------------------------------------------------------------------------------
   102     |   100    |   200    |    300    |    100     |    200     |    300    |
------------------------------------------------------------------------------------

How can i get result like this?

Prashant16
  • 1,514
  • 3
  • 18
  • 39
  • I think this is what you are looking for: http://stackoverflow.com/questions/1578500/how-to-select-columns-as-rows – Yeronimo Apr 19 '13 at 09:11
  • @Yeronimo That will display data horizontal to vertical format but i want to display vertical to horizontal format. – Prashant16 Apr 19 '13 at 09:47
  • Then you probably need Pivot or Stuff: http://stackoverflow.com/questions/10652921/t-sql-group-rows-into-columns – Yeronimo Apr 19 '13 at 09:59

0 Answers0