Hi..How can do a loop statement for the following table
first loop shld be e1,e2,e3....
first loop start
sub loop shld be
for every e1 the loop shld be t1,t2,t3 ....
for every e2 the loop shld be t1,t2,t3 ....
sub loop start
select stmt
sub loop end
first loop end
drop table sample_data
go
create table sample_data
(
id varchar(20) ,
Eid varchar(20),
typ varchar(20),
dur float
)
GO
insert into sample_data (id,Eid,typ,dur) VALUES ( '1','e1','t1','18.00')
insert into sample_data (id,Eid,typ,dur) VALUES ('2','e2','t1','14.00')
insert into sample_data (id,Eid,typ,dur) VALUES ('3','e3','t2','20.00')
insert into sample_data (id,Eid,typ,dur) VALUES ('4','e4','t3','13.00')
insert into sample_data (id,Eid,typ,dur) VALUES ('5','e5','t3','15.00')
GO
SELECT * FROM sample_data
GO
March 5, 2021 at 4:18 am
Forget about loops for a minute. What are you actually trying to accomplish? Please explain.
Like Pietlinden, I am not sure what you are trying to do, but one way you could do what you are asking would be with a cursors and a while loop. Cursor is used to grab the Eid value, then you select each typ for the specified Eid, then grab the next value in the cursor and loop.
I think what would be really beneficial is to see what the data output you expect looks like though. Based on what you said, I doubt that you need to do anything with loops UNLESS you are looking for multiple data sets OR this is some sort of "make work" task (show you know your SQL loops in a homework assignment for example).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 5, 2021 at 4:30 pm
Is this what you want?
SELECT a.Eid , b.typ
FROM sample_data a
CROSS JOIN sample_data b
ORDER BY 1, 2
March 8, 2021 at 10:03 pm
>> .. do a loop statement for the following table <<
Where is the DDL for this table? We don't have loops in SQL! This is a set-oriented declarative language. If this were zoology newsgroup, you'd be the guy talking about gorilla eggs :-). You also don't seem to understand that a table must have a key. By definition. This is not an option . There is no such thing as a generic "id" in RDBMS, but I'm going to guess that's what you were trying to use for a key. None of your column names do not make any sense or follow ISO 11179 naming rules. I also find it amazing that your careful planning and design led to VARCHAR(20) NOT NULL for everything. Surely no competent database designer would just randomly grab a declaration for a column like this. You also don't seem to know that you can do a table constructor in an insertion statement. I'm sure this was a result of careful research, but I've never used float in 30+ years of SQL. But if I did use them I would not input them as strings!
CREATE TABLE Samples
(sample_id VARCHAR(20) NOT NULL PRIMARY KEY, -- requiredired!!,
e_id VARCHAR(20) NOT NULL,
foobar_type VARCHAR(20) NOT NULL,
dur FLOAT NOT NULL);
INSERT INTO Samples
VALUES
('01', 'e1', 't1', 18.00),
('02', 'e2', 't1', 14.00),
('03', 'e3', 't2', 20.00),
('04', 'e4', 't3', 13.00),
('05', 'e5', 't3', 15.00);
We now have a usable chunk of DDL and sample data. Now what were you trying to do?
Please post DDL and follow ANSI/ISO standards when asking for help.
March 10, 2021 at 8:22 pm
declare @Sample_Data table
(
id varchar(20) ,
Eid varchar(20),
typ varchar(20),
dur float
)
insert into @Sample_Data (id,Eid,typ,dur) VALUES ('1','e1','t1','18.00');
insert into @Sample_Data (id,Eid,typ,dur) VALUES ('2','e2','t1','14.00');
insert into @Sample_Data (id,Eid,typ,dur) VALUES ('3','e3','t2','20.00');
insert into @Sample_Data (id,Eid,typ,dur) VALUES ('4','e4','t3','13.00');
insert into @Sample_Data (id,Eid,typ,dur) VALUES ('5','e5','t3','15.00');
select * from @Sample_Data
-- solution
select s1.id,s1.Eid,s2.typ,s2.dur
from @Sample_Data s1
Cross join @Sample_Data S2
order by eid,typ
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply