How can do a loop select stmt from this folowing table

  • 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

     

  • 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).

  • Is this what you want?

    SELECT a.Eid , b.typ
    FROM sample_data a
    CROSS JOIN sample_data b
    ORDER BY 1, 2
  • >> .. 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. 

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply