How to display one column value that has multiple rows?

Question

I have a select query with inner join tables that output like this:

ID | StudentID |   Fullname   | Transaction     | Date    | Place
__   _________   ____________   ______________   ________   _____
1    S-001       Smith,James    Dental Checkup   08-21-13   Clinic A
2    S-002       Blake, David   Dental Checkup   09-25-13   Clinic C
3    S-002       Blake, David   Dental Checkup   10-27-13   Clinic C
4    S-002       Blake, David   Dental Checkup   11-27-13   Clinic C
5    S-003       Curtis, An     Dental Checkup   08-15-13   Clinic B
6    S-003       Curtis, An     Dental Checkup   09-15-13   Clinic B

All I want to display on my report in Visual Basic 6 is only one student name with more than one transactions but all their transactions will be shown, something like this:

ID | StudentID |   Fullname   | Transaction     | Date    | Place
__   _________   ____________   ______________   ________   _____
1    S-001       Smith,James    Dental Checkup   08-21-13   Clinic A
2    S-002       Blake, David   Dental Checkup   09-25-13   Clinic C
3                               Dental Checkup   10-27-13   Clinic C
4                               Dental Checkup   11-27-13   Clinic C
5    S-003       Curtis, An     Dental Checkup   08-15-13   Clinic B
6                               Dental Checkup   09-15-13   Clinic B

Is it possible?

It is only a reporting of students with dental transactions. — ImTheBoss, Jan 03 '14 at 07:48
Are you showing this in a grid? Are you using Crystal reports? — Madhivanan, Jan 03 '14 at 07:50
No I don't, I'm just using the data report in VB6 . I don't know how to use Crystal report. — ImTheBoss, Jan 03 '14 at 07:54

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

You can achieve this using partition by in row_number(). See this link. And read comment section as well.

edited May 23 '17 at 12:28

Community

1
1

answered Jan 03 '14 at 11:15

Amar Mishra

89
2
10

Thanks for this link. I've tested it on my query and it works. Thanks a lot! – ImTheBoss Jan 03 '14 at 16:39

score 0 · Answer 2 · answered Jan 03 '14 at 10:18

Your query should be something like this:

Select *
from students s
inner join transactions t on t.StudentID = t.StudentID
inner join 
(
    select s.StudentID, count(*)
    from students s
    inner join transactions t on t.StudentID = t.StudentID
    group by s.StudentID
    having count(*) > 1
) ms on ms.StudentID = s.StudentID

The inner join called 'ms' only includes students with more than 1 transaction, so will limit the result set to what you want. How you then display them is a different problem: not many grids have the capability to show blanks instead of repeated values in columns, as you've shown with StudentID and FullName, so you may have to do extra work to get that effect if it's absolutely required. (And if it's not absolutely required, I'd try to get out of doing that as it gets messy!)

Cheers -

How to display one column value that has multiple rows?

2 Answers2