SQL Cursor multiply two sets of lines

Question

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

Why do you think you need a cursor? You want to avoid loops for this type of thing. But what I don't understand here is the output. — Sean Lange
– Sean Lange, Commented Apr 5, 2024 at 19:32
I rolled your changes back, we do not want images, text tables are best — Dale K
– Dale K, Commented Apr 5, 2024 at 19:33
No a cursor or any kind of looping is absolutely not the way to go here. — Sean Lange
– Sean Lange, Commented Apr 5, 2024 at 19:45

Sean Lange · Accepted Answer · 2024-04-05 21:48:48Z

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

Collectives™ on Stack Overflow

SQL Cursor multiply two sets of lines

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related