Joining Rows in a SubQuery

I have the following code in a stored procedure
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
order by CategoryName
it's output is similar to this:
CatID | ParentId | CategoryName | pagecount
6 1 Cat1 0
4 Null Cat2 3
I would like to make a new column (say Newtext) adn Return something like th
is
CatID | ParentId | CategoryName | pagecount | NewText
6 1 Cat1 0 Cat1 (0)
4 Null Cat2 3 Cat2 (3)
How can i join the data to have this result?
I would hope that i could do something like CategoryName + "(" + pagecount +
")" in some sort of sql statement.
Thanks for any input
Lots of ways, here's one
SELECT CatID, ParentId, CategoryName,pagecount ,
CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
NewText
FROM
(
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
) X
order by CategoryName|||One approach is to use a derived table, embedding the existing query
in the FROM clause of an outer query:
SELECT *,
NewText =
CategoryName + '(' + convert(varchar(8),pagecount) + ')'
FROM (<query as you stated it> ) as X
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
<Fabuloussites@.discussions.microsoft.com> wrote:

>I have the following code in a stored procedure
>SELECT CatID, ParentId, CategoryName, (select count(*)
>from members WHERE DirectoryCat = DirectoryCats.CatID and
>InFreeDirectory=1 and ApproveDirectory=1) pagecount
>FROM DirectoryCats
>where active=1
>order by CategoryName
>
>it's output is similar to this:
>CatID | ParentId | CategoryName | pagecount
>6 1 Cat1 0
>4 Null Cat2 3
>I would like to make a new column (say Newtext) adn Return something like t
his
>CatID | ParentId | CategoryName | pagecount | NewText
>6 1 Cat1 0 Cat1 (0)
>4 Null Cat2 3 Cat2 (3)
>How can i join the data to have this result?
>I would hope that i could do something like CategoryName + "(" + pagecount
+
>")" in some sort of sql statement.
>Thanks for any input|||thanks for the fast and helpful response.
"markc600@.hotmail.com" wrote:

>
> Lots of ways, here's one
>
> SELECT CatID, ParentId, CategoryName,pagecount ,
> CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
> NewText
> FROM
> (
> SELECT CatID, ParentId, CategoryName, (select count(*)
> from members WHERE DirectoryCat = DirectoryCats.CatID and
> InFreeDirectory=1 and ApproveDirectory=1) pagecount
> FROM DirectoryCats
> where active=1
> ) X
> order by CategoryName
>|||thanks for the fast and helpful response.
"Roy Harvey" wrote:

> One approach is to use a derived table, embedding the existing query
> in the FROM clause of an outer query:
> SELECT *,
> NewText =
> CategoryName + '(' + convert(varchar(8),pagecount) + ')'
> FROM (<query as you stated it> ) as X
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
> <Fabuloussites@.discussions.microsoft.com> wrote:
>
>