SQL challenge

[sasguru]

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

[G8_Summit]

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

[sasguru]

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

Fair enough. Was just settling an argument with a SQL bod. Obviously piece of piss in any prog language.

[scotspine]

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

[expat]

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.

[sasguru]

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

[lilelvis2000]

MS Access crosstab query? Or is that considered cheating.

[jambuie]

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;

[Joe Black]

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.

[mcquiggd]

Advertise it on some indian dominated outsourcing website and offer the price of a can of pepsi... jobs a good un.