Data Pivot Problem

Question

Data Pivot Problem

andyskinner

Ten Centuries

Points: 1256
More actions
November 29, 2002 at 2:53 am

#79490

Can anyone help with this. I could do it in VB, not too much problem but want to do it in TSQL...
I have a table with 2 receipt types identified by a code
i.e.
----
shopID receiptcode amount
------ ----------- -------
2 1 121
2 2 332
4 1 344
4 2 665
Basically, I want to create a new table with this data but want it to look like the following
shopID receipt1 receipt2
------ ----------- ---------
2 121 332
4 344 665
Any ideas greatly received!
Andy.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12541 More actions · Answer 1

First of all, I would like to remark that you are denormalising your tables. But I guess you have a good reason to do so.

Here's a possible solution



INSERT INTO NewTable

 (ShopID, Receipt1, Receipt2)

 (  SELECT t1.ShopID, t1.Amount, t2.Amount

    FROM OldTable t1 LEFT OUTER JOIN 

         OldTable t2 ON 

              t1.shopID = t2.shopID 

              AND t2.receiptcode = 2

    WHERE t1.receiptcode = 1

 )

This will insert a record for each ShopID that has a record with Receiptcode 1. If the shop also has a receiptcode 2, it will also be inserted. If that is not the case, Receipt2 will be NULL in the new table.

Hope this is what you are looking for...

paulhumphris Hall of Fame Points: 3496 More actions · Answer 2

It is possible to pivot data using the case statement.

If the columns are known, you can use this.

select shopid,

sum(case receiptcode

when 1 then amount

else 0

end) as receipt1,

sum(case receiptcode

when 2 then amount

else 0

end) as receipt2

from tblSample

group by shopid

If the columns are unknown you would have to build the statement using dynamic SQL, building it within a cursor of a distinct list of column values.

Edited by - paulhumphris on 11/29/2002 03:31:58 AM

andyskinner Ten Centuries Points: 1256 More actions · Answer 3

Cheers guys, I actually did this a completly different way. I read the data into crystal reports, wrote a few formula`s then exported it to CSV and imported back into SQL....!!

Thanks for the advice anyway, will be useful for future reference.

Andy.