SQLTeam.com | Weblogs | Forums

Create multiple rows based on different column values


#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

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

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.