I need to get a query of data from a large table where I can compare like items with their tax rates

I have a table that has itemno (item number of product) and tax_rate that is in up to 321 stamp groups. The stamp groups are for differing tax rates in cities, counties, and states.

I need to make a query that will show me the tax_rate and stamp_group for each itemno. Since there are multiple rows with each itemno, I want to organize my query so it will show the itemno on the left and the stampgroup across the top and then the tax_rate in the table.

I want to export to an excel spreadsheet to provide to my compliance department so they can scan like items and insure that tax rates are correct.

Thank you in advance for any assistance!

Charles Peterson

SQL Server 2000 doesn't have a PIVOT type operator. This means that you effectively have two options...
1. Hard code the query with all 321 stamp groups
2. Return the results in a normalised form, and have Excel's VBA put it into columns

I would err on the side of the latter, as it feels more about presentation than data. But if the former is necessary, it would look something like...

SELECT
  itemno,
  MAX(CASE stamp_group WHEN '1' THEN taxrate ELSE NULL END) AS taxrate_1,
  MAX(CASE stamp_group WHEN '2' THEN taxrate ELSE NULL END) AS taxrate_2,
  MAX(CASE stamp_group WHEN '3' THEN taxrate ELSE NULL END) AS taxrate_3
FROM
  myTable
GROUP BY
  itemno

This assumes that the taxrate is numeric, and that each combination of (itemno, stamp_group) has, at most, one taxrate.

(Note: The MAX() will see a whole bunch of NULLs from the CASE statement, and just a single non-NULL value.)

If the taxrate is not numeric, MAX() won't like it. In which case I'd suggest creating a lookup table with the taxrates, enabling you to give each taxrate a numeric id. Then you can use that ID.

Also consider using an Access database and their crosstab wizard to do the pivot for you, or you can pivot easily in Excel. SQL is not the greatest tool to use for pivoting data.