November 27, 2013 at 10:01 am
Hi,
I have a table with following structure:
SalesRow
SaleId INT, SaleRowId, TaxCode INT, TaxValue INT, Price INT, ExtraTaxCode INT, ExtraTaxValue INT
On a report I need to output the different taxes but with only one column:
SaleId | TaxaCode | SUM(TaxValue) | SUM(Price)
Currently I'm making an UNION ALL to the table with itself to get Tax and ExtraTax in same column:
SELECT SaleId, Tax, SUM(TaxValue), SUM(Price) FROM (
SELECT SaleId, TaxCode, TaxValue, Price FROM SalesRows
UNION ALL
SELECT SaleId, ExtraTaxCode, ExtraTaxValue, Price FROM SalesRows WHERE ExtraTaxCode <> 0 AND ExtraTaxValue <> 0
) t GROUP BY SaleId, Tax
Is it possible to have just a SELECT and check if ExtraTaxCode <> 0 AND ExtraTaxValue <> 0 "duplicate" the row with it's values?
Thanks,
Pedro
PS: TaxCode and TaxValue always have data, ExtraTaxCode and ExtraTaxValue don't...
November 27, 2013 at 12:34 pm
Hi
I think this will do what you want. Inspired by Dwain's article on Alternative unpivot method[/url]
SELECT s.SaleId, t.TaxCode, SUM(t.TaxValue), SUM(s.Price)
FROM SalesRows s
CROSS APPLY (VALUES (TaxCode, TaxValue), (ExtraTaxCode, ExtraTaxValue)) t(TaxCode, TaxValue)
WHERE t.TaxCode <> 0
GROUP BY s.SaleId, t.TaxCode
ORDER BY s.SaleID
November 28, 2013 at 2:22 am
mickyT (11/27/2013)
HiI think this will do what you want. Inspired by Dwain's article on Alternative unpivot method[/url]
SELECT s.SaleId, t.TaxCode, SUM(t.TaxValue), SUM(s.Price)FROM SalesRows s
CROSS APPLY (VALUES (TaxCode, TaxValue), (ExtraTaxCode, ExtraTaxValue)) t(TaxCode, TaxValue)
WHERE t.TaxCode <> 0
GROUP BY s.SaleId, t.TaxCode
ORDER BY s.SaleID
Thanks, works like a charm...
I was thinking doing a CROSS APPLY with VALUES (1), (2) but it creates a worktable and with the CROSS APPLY with itself it's just perfect...
Many thanks,
Pedro
November 28, 2013 at 10:50 am
No problem:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply