March 12, 2007 at 9:57 pm
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
March 13, 2007 at 1:38 am
what r u going 2 do after getting such result.. its seems ridiculous b4 thinking
March 13, 2007 at 1:47 am
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.
March 13, 2007 at 7:36 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 13, 2007 at 8:10 am
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.
March 13, 2007 at 6:43 pm
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