Split records by whether or not multiple columns have values-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

In SQL Server, I would recommend unpivoting with a lateral join. This is a cross-database syntax, which is much more flexible than the vendor-locked pivot query:

select t.tax_id, x.class, x.value, 
    row_number() over(partition by t.tax_id order by x.class) seq 
from mytable t
cross apply (values 
    ('1',  class1), ('2',  class2), ('3',  class3), ('4A', class4a), ('4B', class4b)
) x(class, value)
where x.value <> 0
order by t.tax_id, seq

outer apply unpivots the columns to rows, and assigns the final class; all that is left to do is filter out 0 values. The sequence can be easily generated with row_number.