[mrburnttoast]

I am rewriting an application where I am just displaying contact info into a gridview. I've got it all done except I am trying to display like so:

employee1 title1 number
_________ title2_________
_________ title3_________
employee2 title1 number

etc....

The previous version used one table and included blank fields to format correctly. Now I am required to use an Employees table and the titles are coming from a joined table. All I know how to do is display the full record with name title number on each line. Is it possible to do what I am looking for using a count function or something? Any help is appreciated.

[Atli]

Hey.

Ideally this type of formatting should be done by the code that presents the data, not the database itself. What you are asking is possible, but it is usually far simpler to do things like this in the front-end application.

Depending on which type of database and programming language you are using, you may simply be able to remove repeated "employee1" and "number" values before filling in the gridview in your code, or go over it after filling it out.


If you insist on doing this in the SQL query, you could use a function or a procedure to mess with the result set before it is returned. You might also be able to do some filtering in the SQL query itself. For example, in MySQL a query like this might work:

SELECT
    (CASE e.employee1
        WHEN @lastEmployee THEN ""
        ELSE e.number
    END) as 'number',
    (CASE e.employee1
        WHEN @lastEmployee THEN ""
        ELSE @lastEmployee := e.employee1
    END) as 'employee1',
    e.title1
FROM employees AS e
ORDER BY e.employee1

[mrburnttoast]

Atli, on 29 December 2011 - 12:54 AM, said:

Hey.

Ideally this type of formatting should be done by the code that presents the data, not the database itself. What you are asking is possible, but it is usually far simpler to do things like this in the front-end application.

Depending on which type of database and programming language you are using, you may simply be able to remove repeated "employee1" and "number" values before filling in the gridview in your code, or go over it after filling it out.


If you insist on doing this in the SQL query, you could use a function or a procedure to mess with the result set before it is returned. You might also be able to do some filtering in the SQL query itself. For example, in MySQL a query like this might work:

SELECT
    (CASE e.employee1
        WHEN @lastEmployee THEN ""
        ELSE e.number
    END) as 'number',
    (CASE e.employee1
        WHEN @lastEmployee THEN ""
        ELSE @lastEmployee := e.employee1
    END) as 'employee1',
    e.title1
FROM employees AS e
ORDER BY e.employee1

I am using an asp.net gridview to display the results. Basically I have a dozen links on the left menu. Clicking each one displays different results in the grid. I am using one gridview and auto generating rows because different datasets contain different numbers of columns. This is the only one that has blank fields in it. This is the only reason I was trying to use SQL to manipulate the results.