I have a requirement where I need to create rows based on different column values using a select query.
So let's say the Invoice table has columns GSTTaxable, HSTTaxable, PSTTaxable, NonTaxable
If GSTTaxable or HSTTaxablehas value > 0, I need to create 2 rows using values in these columns.
If PSTTaxable has value > 0, I need to create 2 rows I need to create 2 rows using value in the column.
Same thing for NonTaxable.
And it is joined with InvoiceItems on one to one relationship, and other tables - probably not the focus here and not worth providing detail
I looked at UNPIVOT, VALUES etc options but could not match it with my scenario.
Any ideas will be appreciated.