IF EXISTS with SQL Server 2008

Question

I have a table order in which there are 3 columns

  BookCode  Quantity  Billno
  --------------------------    
   Ed001        2      A1
   Ed002        3      A1
   Ed003        1      A1
   py001        5      A2
   ed001        1      A2
   ed005        2      A2
   ed003        4      A2

Now I want to show my result to user as

table Result

   BookCode   A1     A2
   ----------------------    
   Ed001      2      1
   Ed002      3      0
   Ed003      1      4
   py001      0      5
   ed005      0      1

Result is my temporary table, I alter it and add column according to Billno

this works fine

Now I select BookCode and Quantity from table Order and insert it into result

What I want that if there is already a bookcode exist then just update result table otherwise insert a new row with that bookcode.

What should I do for that?

Answer 1

I solved it using Cursor..

create table #Result(BookCode varchar(20),BookRate float)
create table #bill(bilno varchar(max) )
insert into #bill ('A1')
insert into #bill ('A2')

    SET @Ordnum = CURSOR FOR
        SELECT bilno FROM #bill 
                OPEN @Ordnum
            FETCH NEXT
            FROM @Ordnum INTO @billno
                WHILE @@FETCH_STATUS = 0
                 BEGIN                  
                        EXEC ('ALTER TABLE #temp ADD [' + @billno+ '] varchar(30)') 
                            SET @getbookcode = CURSOR FOR
                                SELECT BookCode , BookQuantity , BookRate FROM OrderDetail where BillNo = @billno
                            OPEN @getbookcode
                                FETCH NEXT
                                    FROM @getbookcode INTO @Tbookcode,@TbookQty,@TbookRate
                                        WHILE @@FETCH_STATUS = 0
                                        BEGIN
                                             if exists(select BookCode from #Result where BookCode = @Tbookcode and BookRate = @TbookRate)
                                                begin
                                                    EXEC('Update #Result set [' + @billno+']= '+@TbookQty+' where BookCode = '''+@Tbookcode+''' and  BookRate = '+@TbookRate+'')                 
                                                end                          
                                             else 
                                                begin
                                                    EXEC('Insert Into #Result ([' + @bno + '],BookCode,BookRate) values ( '+@TbookQty +' , '''+@Tbookcode+''','+@TbookRate+')')                                  
                                                end              
                                            FETCH NEXT FROM @getbookcode INTO @Tbookcode,@TbookQty , @TbookRate
                                        END
                                    CLOSE @getbookcode
                                    DEALLOCATE @getbookcode                              
                    FETCH NEXT FROM @Ordnum INTO @bno
                 END
                CLOSE @Ordnum
            DEALLOCATE @Ordnum

 select * from #Result

Here #bill is a Temporary table in which Billnum are stored.

Answer 2

Use the MERGE statement. This does an INSERT or UPDATE based on the matching

Something like this, which includes the PIVOT needed

MERGE INTO result R
USING (
        SELECT
              ISNULL(O1.BookCode, O2.BookCode) AS BookCode,
              ISNULL(O1.Quantity, 0) AS A1,
              ISNULL(O2.Quantity, 0) AS A2
        FROM
              (SELECT * FROM #ORDER WHERE BillNo = 'A1') O1
              FULL OUTER JOIN
              (SELECT * FROM #ORDER WHERE BillNo = 'A2') O2
                                 ON O1.BookCode = O2.BookCode
    ) src ON R.BookCode = src.BookCode
WHEN MATCHED THEN 
    UPDATE
    SET A1 = src.A1, A2 = src.A2
WHEN NOT MATCHED BY TARGET THEN
   INSERT (BookCode, A1, A2)
   VALUES (src.BookCode, src.A1, src.A2);