Displaying one id with many records 2

[chinedu]

Hi all,
I have done this before but it's been so long that I don't remember how I did, neither could I find the code for what I am looing for.

I have a one to many situation where one ID could have one or more records associated with it.
In my case, the maximum number of records that can be associated each ID value is 4.

What I would like to do for the user is rather than display the records like this:

ID file
1 file1
1 file2
1 file3
1 file4 etc,
I will like to display just one ID like
1 and then all the files associated with are displayed.

For instance:

ID file
1 file1
file2
file3
file4
_______________________________
2 file1
2 file2
____________________________
3 file1
_________________________________
4 file1
4 file2
4 file3
___________________________________
I hope you get the jist.

so far I can't seem to get started on it.

Any help would be greatly appreciated.

 

[nigelrivett]

select id = case when seq = 1 then convert(varachar(10),id) else '' end ,
file
from
(
select id , file, seq = (select count(*) from tbl t2 where t1.id = t2.id and t2.file <= t1.file)
from tbl t1
) a
order by a.id, a.file


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

Nice work Nigel.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

BTW, Nigel, your list of contributions to this forum is exemplary. Would you willing to add your thoughts to "Recommendations for MSSQL coding conventions thread183-856683"?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Beautiful! You beat my solution hands down. thread183-859372

Interested readers might also like to see FAQ183-5021.

 

[chinedu]

hi nigel,
thank you very much for this code.
Can I ask a couple of more questions?
What is the seq?

Second, can I use this code with access db?
I am currently using for sql server, though.

 

[ESquared]

seq is an alias for the column. You might have to do

(select count(*) from tbl t2 where t1.id = t2.id and t2.file <= t1.file) as seq

 

[ESquared]

I think I have something wrong and I'm not at work to test it out... ignore what I said for the time being, okay? Thanks!

 

[donutman]

EE, drop the "as".

 

[donutman]

Oh darn...I need to slow down. That wasn't a derived table reference. So do as ESquared says, "ignore what I said