CODEWITHSUNDEEP
sqlunionhanacartesian-productdatabase-cursor
Frankie Contreras
Frankie Contreras

Reputation: 1

SQL Cursor multiply two sets of lines

I am using Hana data studio (SQL) and need to write a cursor that takes the original dataset, makes column age set to 0 and sets the the fiscal quarter to the iterated value over a distinct list of fiscal quarters in the input data set (FY24Q1, FY24Q2, FY24Q3) and unions it to the original dataset for every distinct fiscal quarter.

Input sample data set:

Name Age Fiscal_Quarter
James 22 FY24Q1
Henry 32 FY24Q2
Ethan 45 FY24Q1
Jake 19 FY24Q3

Output:

Name Age Fiscal_Quarter
James 22 FY24Q1
Henry 32 FY24Q2
Ethan 45 FY24Q1
Jake 19 FY24Q3
James 0 FY24Q1
Henry 0 FY24Q1
Ethan 0 FY24Q1
Jake 0 FY24Q1
James 0 FY24Q2
Henry 0 FY24Q2
Ethan 0 FY24Q2
Jake 0 FY24Q2
James 0 FY24Q3
Henry 0 FY24Q3
Ethan 0 FY24Q3
Jake 0 FY24Q3

Upvotes: 0

Views: 60

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

This is a truly bizarre requirement but definitely don't need any loops here. There are multiple ways this could be solved, here is one of them.

First I need to create a table to work with. This could be your existing table.

create table #Something
(
    Name varchar(10)
    , Age int
    , Fiscal_Quarter varchar(10)
)
insert #Something
values
('James', 22, 'FY24Q1')
, ('Henry', 32, 'FY24Q2')
, ('Ethan', 45, 'FY24Q1')
, ('Jake', 19, 'FY24Q3');

Now that there is a table to work with we just a query to get the existing data and then a cross join of Names and Quarters.

Something like this should work. It produces the desired output in your question from your sample data.

select Name
    , Age
    , Fiscal_Quarter
from #Something

union all

select Name
    , 0
    , Fiscal_Quarter
from
(
    select distinct Name from #Something
) n
cross join
(
    select distinct Fiscal_Quarter from #Something
) q

Upvotes: 1

Related Questions