Split records by whether or not multiple columns have values

Question

I need to set up a query that will split up a single record into multiple records based on values from multiple columns in a single table.

Right now, a current parcel record would read as:

table.tax_id    table.CLASS 1   table.CLASS 2   table.CLASS 3   table.CLASS 4A  table.CLASS 4B
03489                       0                        100                     0                       0                        600
05695                       0                         0                    100                     300                          0

I need to generate a sequence number for each record and then split them up according to class, so the above parcels would look like this instead:

table.tax_id    table.CLASS           table.VALUE   table.SEQUENCE
03489                     2                   100                1 
03489                    4B                   600                2
05695                     3                   100                1
05695                    4A                   300                2

I've tried CASE and IIF statements but couldn't get any of them to work. Any suggestions are very appreciated!

Answer 1

You can UNPIVOT your data, here's an example query:

SELECT [table.tax_id],
    REPLACE([table.CLASS],'table.CLASS ','') [table.CLASS],
    [table.VALUE],
    ROW_NUMBER() OVER (PARTITION BY [table.tax_id] ORDER BY REPLACE([table.CLASS],'table.CLASS ','')) AS [table.SEQUENCE]
FROM   
   (SELECT 03489 as [table.tax_id], 0 AS [table.CLASS 1],100 as [table.CLASS 2],0 as [table.CLASS 3],0 AS [table.CLASS 4A],600 AS [table.CLASS 4B]
    UNION ALL 
    SELECT 05695,0,0,100,300,0) p  
UNPIVOT  
   ([table.VALUE] FOR [table.CLASS] IN   
      ([table.CLASS 1],[table.CLASS 2],[table.CLASS 3],[table.CLASS 4A],[table.CLASS 4B])  
)AS unpvt  
WHERE [table.VALUE] <> 0
GO