How to rid of duplicate row headers

  • I would like to query a table which when it yields the result it should look something like this:

    Name Subjects

    A X

    Y

    Z

    B Z

    P

    etc.

    At the moment, when I do a select * from the table it gets me something like this:

    Name Subjects

    A X

    A Y

    A Z

    B Z

    B P

    How could I achieve the top dataset?

    Thanks

  • what r u going 2 do after getting such result.. its seems ridiculous b4 thinking

  • The results are output in an excel report.

    When it is output in that format, it is not ridiculous anymore. The clarity of data is what is achieved, and what I am after.

    If it is just output as standard, an excel report is untidy.

  • Wouldn't you need a placeholder for the first column in Excel?

    SQL doesn't have the concept of a record, so finding the 2nd, 3rd, etc. occurrance is difficult. I'm not sure it's possible without some crazy ordering scheme. I think I can do it with a temp table or a third column for ordering, but it would not be a simple query.

  • Providing that Subjects in ASC order for [Name] does not matter then

    SELECT

    CASE WHEN a.Subjects1 = b.Subjects2 THEN a.Name1 ELSE a.Subjects1 END AS [Name],

    CASE WHEN a.Subjects1 = b.Subjects2 THEN a.Subjects1 ELSE '' END AS [Subjects]

    FROM (SELECT [Name] AS [Name1],Subjects AS [Subjects1] FROM

    ) a

    INNER JOIN (SELECT [Name] AS [Name2],MIN(Subjects) AS [Subjects2] FROM

    GROUP BY [Name]) b

    ON b.[Name2] = a.[Name1]

    ORDER BY a.[Name1] ASC ,a.Subjects1 ASC

    Far away is close at hand in the images of elsewhere.
    Anon.

  • That seems to be alright.

    A self join does do the trick.

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply