November 29, 2002 at 2:53 am
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.
November 29, 2002 at 3:08 am
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...
November 29, 2002 at 3:31 am
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
November 29, 2002 at 3:34 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply