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!

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Nov 09 '22 at 15:22
The table schema is poor. Your best move is to first use a table scheme like your desired output and migrate all the data to it using app code or a stored procedure. — Bohemian, Nov 09 '22 at 16:08

score 0 · Accepted Answer · answered Nov 09 '22 at 16:12

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

This was exactly what I was looking for! Thanks so much! I only had to modify the code slightly so that it would work for my data: SELECT [p_id], tax_map, REPLACE([#TempTax.CLASS],'CLASS ','') [CLASS], [#TempTax.VAL], ROW_NUMBER() OVER (PARTITION BY [p_id] ORDER BY REPLACE([#TempTax.CLASS],'CLASS ','')) AS SEQUENCE FROM (SELECT P_ID, TAX_MAP, CLASS_3, CLASS_4A, CLASS_4B FROM #TempTax) P UNPIVOT ([#TempTax.VAL] FOR [#TempTax.CLASS] IN ([CLASS_3],[CLASS_4A],[CLASS_4B]) )AS unpvt WHERE [#TempTax.VAL] <> 0 GO — Cbrown88, Nov 09 '22 at 22:07

score 0 · Answer 2 · answered Nov 09 '22 at 23:07

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.

Split records by whether or not multiple columns have values

2 Answers2