Create multiple rows based on different column values

Transact-SQL
1

Hello,

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.
Thanks.

2 
create table #sqlbug(GSTTaxable money, HSTTaxable money, 
PSTTaxable money, NonTaxable money)

insert into #sqlbug
select 2,3,4,5 union
select 2,3,4,5

please provide sample data to work with?

3

create table #Invoice(IID int, GSTTaxable money, HSTTaxable money,
PSTTaxable money, NonTaxable money)

insert into #Invoice
select 1, 0, 234.50, 0,0 union
select 2, 3049.79, 0, 1200.25, 0 union
select 3, 200.00, 0, 100.00, 50.00

So for ID 1, the Select should create 2 rows
Row 1 - 234.50,
Row 2 - 234.50/97*3 (some calculation)

For ID 2, we should have 4 rows:
Row 1 - 3049.79,
Row 2 - 3049.79/97*3
Row 3 - 1200.25
Row 4 - 1200.25/# (some calculation)

For ID 3, we should have 6 rows:
Row 1 - 200.00,
Row 2 - 200.00/97*3
Row 3 - 100.00
Row 4 - 100.00/# (some calculation)
Row 5 - 50.00
Row 6 - 50/# (some calculation)

The IID will be passed as a parameter, therefore, we don't need to worry about combining these different number of rows in the same query.

Is this what you wanted? Thanks.

4

take #1

create table #Invoice(IID int, GSTTaxable money, HSTTaxable money,
PSTTaxable money, NonTaxable money)

insert into #Invoice
select 1, 0, 234.50, 0,0 union
select 2, 3049.79, 0, 1200.25, 0 union
select 3, 200.00, 0, 100.00, 100.00

;with cte
as
(
SELECT IID, TaxType, Taxes  
FROM   
   (select * 
  From #Invoice i
 where i.IID = 3) p  
UNPIVOT  
   (Taxes FOR TaxType IN   
      (GSTTAXABLE, HSTTaxable, PSTTaxable, NonTaxable)  
)AS unpvt
where Taxes <> 0

)
select Taxes 
  From cte
  where Taxes > 0
union all
select (Taxes/97)*3  
 from cte
 where Taxes > 0
 order by Taxes
;  
GO  

drop table #Invoice
5

Hi yosiasz,
Question: why i.IID = 3 in the following part:
;with cte
as
(
SELECT IID, TaxType, Taxes
FROM
(select *
From #Invoice i
where i.IID = 3) p

Thanks.

6

because you said that value comes as a parameter

7

Got it, thank you very much yosi. :smiley:

8

HI

i think its possible with CROSS JOIN

would you like me to TRY ? :slight_smile:

9

Hi Haish,
I thought of CROSS JOIN as well.
But off course I will appreciate your input.
Thanks. :grin:

10

hi

I tried cross join

looks like it will not work ..

:frowning::-1: to cross join

:slight_smile:

11

Thanks for trying for me Harish..:smile:

12

you can write a stored procedure with parameter @ID and add code under it.Then instead of IID= 3 write IID = @ID.thereby at run time you can pass IID= 1 OR 2 OR 3

13

Hi sqlbug

This time
I have tried using this with cross join
Please see if its okay ????

/* Its also possible with TALLY Table /
/ Its also possible with TALLY Table */

Earlier i tried with cross join but was not able

Still some work needs to be done for the calculations part

Result

image
image.png1352×764 13.7 KB

create data script along with SQL Query to get result 
USE tempdb 

go 

DROP TABLE #invoice 

go 

CREATE TABLE #invoice 
  ( 
     iid        INT, 
     gsttaxable MONEY, 
     hsttaxable MONEY, 
     psttaxable MONEY, 
     nontaxable MONEY 
  ) 

go 

INSERT INTO #invoice 
SELECT 1, 
       0, 
       234.50, 
       0, 
       0 
UNION 
SELECT 2, 
       3049.79, 
       0, 
       1200.25, 
       0 
UNION 
SELECT 3, 
       200.00, 
       0, 
       100.00, 
       50.00 

go 

; 
WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    partition BY iid 
                    ORDER BY iid) AS rn, 
                a.* 
         FROM   (SELECT a.iid, 
                        a.gsttaxable, 
                        a.hsttaxable, 
                        a.nontaxable, 
                        a.psttaxable 
                 FROM   #invoice a 
                        CROSS JOIN #invoice b 
                        CROSS JOIN #invoice c) a) 
SELECT * 
FROM   cte 
       JOIN (SELECT iid, 
                    CASE WHEN gsttaxable <> 0 THEN 1*2 ELSE 0 END + CASE WHEN 
                               hsttaxable <> 
                                    0 THEN 1 
                    *2 ELSE 0 END + CASE WHEN nontaxable <> 0 THEN 1*2 ELSE 0 
                    END + 
                               CASE 
                                    WHEN 
                    psttaxable <> 0 THEN 1*2 ELSE 0 END AS oks 
             FROM   #invoice) a 
         ON cte.iid = a.iid 
            AND cte.rn <= a.oks 

go
14

Hi

This time i tried with tally table
GOT IT

Still some work needs to be done for the calculations part

Result

image
image.png1077×809 13.2 KB

create data AND solution SQL script 
USE tempdb 

go 

DROP TABLE #invoice 

go 

CREATE TABLE #invoice 
  ( 
     iid        INT, 
     gsttaxable MONEY, 
     hsttaxable MONEY, 
     psttaxable MONEY, 
     nontaxable MONEY 
  ) 

go 

INSERT INTO #invoice 
SELECT 1, 
       0, 
       234.50, 
       0, 
       0 
UNION 
SELECT 2, 
       3049.79, 
       0, 
       1200.25, 
       0 
UNION 
SELECT 3, 
       200.00, 
       0, 
       100.00, 
       50.00 

go 

; 
WITH cte 
     AS (SELECT 1 AS rn 
         UNION 
         SELECT Row_number() 
                  OVER ( 
                    ORDER BY (SELECT NULL)) 
         FROM   (VALUES(2), 
                       (2), 
                       (2)) a(n) 
                CROSS JOIN (VALUES(2), 
                                  (2)) c(n)) 
SELECT a.* 
FROM   cte 
       JOIN (SELECT iid, 
                    gsttaxable, 
                    hsttaxable, 
                    psttaxable, 
                    nontaxable, 
                    CASE WHEN gsttaxable <> 0 THEN 1*2 ELSE 0 END + CASE WHEN 
                               hsttaxable <> 
                                    0 THEN 1 
                    *2 ELSE 0 END + CASE WHEN nontaxable <> 0 THEN 1*2 ELSE 0 
                    END + 
                               CASE 
                                    WHEN 
                    psttaxable <> 0 THEN 1*2 ELSE 0 END AS oks 
             FROM   #invoice) a 
         ON cte.rn <= a.oks 
ORDER  BY 1 

go
15

Thank a lot Harish for the hard work. :smile:
your script is interesting.