Jason,

Your query still does not group the data into one row per instanceid.

it looks like this:

417 Application1

417 Application2

418 Application1

Thanks for the link! I always learn a lot. This SQL worked for me. It has a listing of Server, Instance, and associated applications.

declare @data Table

(

applicationid integer not null,

instanceid...

Thanks Jason.

I gave the row a name 'test' and then it ran.

It adds a new row for each application.

instanceid application

417row test=",Application1

417row test=",Application2

418row test=",Application3

instead of

417 ...

Thanks for responding Lowell.

My goal is to have one row per instance that has a comma separated list of applications. To accomplish that I used to coalesce method in the...

ah ok. I was thinking if I did that, it would only group by the first digits before the . instead of each full rowid.

Thanks again Jason.

One more hang up.

Some of the rowid have multiple decimal places. When it gets to those, it tells me it can't convert varchar to float for floor(rowid).

What does that mean?

Perfect. You guys are amazing. I always learn so much in these forums.

Thanks both of you for helping!

Howard

Thanks Jason. That worked and I really learned a lot.

If you don't mind, here is the way that I really have to output it.

The rowid for these network devices...

I was expecting for 1.1 that it would be a decimal as 4 of 5 were up.

Sorry it was not clear. I made this table just now to demonstrate on a very basic level what it looks like.

/****** Object: Table [dbo].[test] Script...

I have a long list of devices and need to know the availability for each of them.

Thanks for responding Jason.

I get this:

Msg 8120, Level 16, State 1, Line 1

Column 'CustomPollerStatistics_Detail.RowID' is invalid in the select list because it is not contained in either an aggregate function...

Thanks Kevin. Makes sense now.

Thanks Stefan. I see what you are saying and will experiment with it.

Howard

It does not work without the X. I don't understand in what part of the query execution it is referencing the subquery by alias.