>Can you give an example of the syntax? I'm decent with SQL but I haven't had to do too many complicated queries. thanks.
When you know there will be always 4 result columns
select a.*,
b.group as group2, b.expr2,
c.group as group3, c.expr3,
d.group as group4, d.expr4
from
(SELECT Count(cat) as Expr1, cat, group
FROM dbo.TestTable
where cat = '1'
Group By group, cat) a
full join
(SELECT Count(cat) as Expr2, cat, group
FROM dbo.TestTable
where cat = '2'
Group By group, cat) b
on b.group = a.group
full join
(SELECT Count(cat) as Expr3, cat, group
FROM dbo.TestTable
where cat = '3'
Group By group, cat) c
on c.group = a.group or c.group = b.group
full join
(SELECT Count(cat) as Expr4, cat, group
FROM dbo.TestTable
where cat = '4'
Group By group, cat) d
on d.group = a.group or d.group = b.group or d.group = a.group
Now you have to check which one of the fields group, group2, group3 or group4 has non NULL value in order to display it. If you have no way to do this in ASP it could be done putting this code as a subquery of another that gets only one column for Group.
Venelina Jordanova
Outsourcing IT Services Ltd.