• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL challenge

G8_Summit said:
Since when did looping become part of SQL ??

Looping is programmatical - thats why I said use a program.

Thinking maybe I could do this with a cursor ...
 
Thinking definitely you could do it with a cursor.

Simple loop, counting events until change of name, all the while building your list of event names.

But you would have to use a program.

I could do it in Cobol for you :D
 
G8_Summit said:
Thinking definitely you could do it with a cursor.

Simple loop, counting events until change of name, all the while building your list of event names.

But you would have to use a program.

I could do it in Cobol for you :D

Fair enough. Was just settling an argument with a SQL bod. Obviously piece of piss in any prog language.
 
declare @mob_rows varchar(260)
select @rowindex = 1
select @mob_rows = ''
while @rowindex < 8
begin -- row loop
select @colindex = @lowercollimit
select @single_row = ''
while @colindex < @uppercollimit
begin -- column loop
select @t = count(*)
from #tempneeds
where
TypeID = 1 -- page
and QuestionID = @rowindex -- rows
and HelpTypeID = @colindex -- columns
and Need = 'y'
select @colindex = @colindex + 1
select @single_row = @single_row + ' ' + ltrim(str(@t)) -- build up the row
end -- column loop
select @rowindex = @rowindex + 1
select @mob_rows = @mob_rows + '/' + ltrim(@single_row) -- concatenate the rows
end -- row loop
 
sasguru said:
Thinking maybe I could do this with a cursor ...
which is a program, not pure SQL.

OK, it doesn't matter whether it's a yellow cat or a black cat, as long as it catches mice. But it wasn't quite the question; and the difference does point out something crucial about SQL: it's non-procedural.
 
scotspine said:
declare @mob_rows varchar(260)
select @rowindex = 1
select @mob_rows = ''
while @rowindex < 8
begin -- row loop
select @colindex = @lowercollimit
select @single_row = ''
while @colindex < @uppercollimit
begin -- column loop
select @t = count(*)
from #tempneeds
where
TypeID = 1 -- page
and QuestionID = @rowindex -- rows
and HelpTypeID = @colindex -- columns
and Need = 'y'
select @colindex = @colindex + 1
select @single_row = @single_row + ' ' + ltrim(str(@t)) -- build up the row
end -- column loop
select @rowindex = @rowindex + 1
select @mob_rows = @mob_rows + '/' + ltrim(@single_row) -- concatenate the rows
end -- row loop

Cheers Scotspine. Will tell the lazy SQL guy to get on with it :D
 
MS Access crosstab query? Or is that considered cheating. :rolleyes:
 
If your using Oracle 9i or above the following will work.

create table list_test
(person varchar(10),
event varchar(10));

INSERT INTO LIST_TEST ( PERSON, EVENT ) VALUES ( 'person1', 'event1');
INSERT INTO LIST_TEST ( PERSON, EVENT ) VALUES ( 'person1', 'event2');
INSERT INTO LIST_TEST ( PERSON, EVENT ) VALUES ( 'person1', 'event3');
INSERT INTO LIST_TEST ( PERSON, EVENT ) VALUES ( 'person2', 'event1');
INSERT INTO LIST_TEST ( PERSON, EVENT ) VALUES ( 'person2', 'event2');
commit;

select person,
lv count,
events_list
from (select b.*, max(lv) over (partition by person) max_lv
from (
select id,
person,
parent_id,
level lv,
substr(sys_connect_by_path(event, '/'), 2) events_list
from (select rownum id,
person,
event,
nvl(lag(rownum) over (partition by person order by event desc), -1) parent_id
from list_test) a
connect by prior parent_id = id
)b
)
where lv = max_lv;
 
If it's Oracle then I believe they have various non-Ansi functions to make this easier.

If it's SQL Server then you can still do this sort of thing using a stored proc, a temp table or two and some dynamic SQL...did it at my last job to create a query which would 'pivot' an unknown number of row values into columns...unfortunately I don't have the SQL to hand.

Wouldn't recommend doing it with cursors though, they're a lot, lot slower even compared to dynamic SQL, since the query engine never gets a chance to optimise things.
 
Advertise it on some indian dominated outsourcing website and offer the price of a can of pepsi... jobs a good un.
 

Advertisers

Back
Top