November 15, 2007 at 6:33 am
(SQL2000). I have a query ,the output of which I'd like to reformat:
select distinct b.nodename,a.application from Host b inner join appl a
on a.nodeid=b.nodeid
group by b.nodename,a.application
order by b.nodename,a.application
nodename1Application A
nodename1Application B
nodename1Application C
nodename2Application A
nodename2Application c
nodename2Application e
Is there any way of reformatting the output to:
nodename1Application A
Application B
Application C
nodename2Application A
Application c
Application e
To clarify the above I simply want to show the Nodename once for each Nodename in the result set.
Thanks in advance.
November 15, 2007 at 6:51 am
You should really really do this on your client. SQL queries return a sequence of rows (which should even be handled as sets or bags in most cases). The information in a row should be self contained, it should not depend on the previous row's data....
Anyway, it is very bad, but here is an example for how you could do this:
CREATE TABLE mytable (a INT, b INT)
GO
INSERT INTO mytable VALUES (1,1)
INSERT INTO mytable VALUES (1,2)
INSERT INTO mytable VALUES (1,3)
INSERT INTO mytable VALUES (2,1)
INSERT INTO mytable VALUES (2,2)
INSERT INTO mytable VALUES (3,1)
GO
SELECT CASE WHEN row_number() OVER ( PARTITION BY a ORDER BY b ) = 1 THEN a
ELSE NULL
END
, b
FROM mytable
GROUP BY a, b
Once again, this is not the right approach 🙂
Regards,
Andras
November 15, 2007 at 6:57 am
Sorry, I've just noticed that you would like to do this on 2000. My query would work on 2005. You can implement row_number() on 2000 (see http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133) but that is just more horrible). Yet another reason for doing this in the client application, at the end of the day SQL is not for visualizing results 🙂
Andras
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply