Very Computer

Is there a way that I can run a query, with a group by and return the values
in a single record?

Example

    This query may return 4 records
    Select Field1, Count(*)
        From Table1
        Group By Field 1

        Field1:        Count(*)
           1                  25
           2                 10
           3                  23
           4                    4

I want to take those results and return a SINGLE record query with a colume
for each record
   Example:
     Colume Name -->        1        2        3        4
     Record Values -->       25     10      24       4

Is there a way to perform this function?

Any info would be appreciated!

Thanks
Lee

We seem to be working on nearly the same problem. I've been working on a
cursor solution to this, but have run into a roadblock. The code goes
something like this:

SELECT szContent, szTitle FROM tblActionData

OPEN csrActionData



Close..etc.

First of all, not dynamic. I suppose there may be some array solution to
this though... the real problem is that apparently you can't use a variable
as a column alias.

Any ideas anyone?

--
Chris Behrens

http://www.deltav.net

Quote:> Is there a way that I can run a query, with a group by and return the
values
> in a single record?

> Example

>     This query may return 4 records
>     Select Field1, Count(*)
>         From Table1
>         Group By Field 1

>         Field1:        Count(*)
>            1                  25
>            2                 10
>            3                  23
>            4                    4

> I want to take those results and return a SINGLE record query with a
colume
> for each record
>    Example:
>      Colume Name -->        1        2        3        4
>      Record Values -->       25     10      24       4

> Is there a way to perform this function?

> Any info would be appreciated!

> Thanks
> Lee

Lee,

Assuming you know in advance the maximum number of rows that will be
returned using the GROUP BY (and assuming of course that the maximum number
of rows is a manageable number), you might consider the following:

Select Column1 = sum (case when Field1 = 1 then cnt else 0 end),
            Column2 = sum (case when Field1 = 2 then cnt else 0 end),
            Column3 = sum (case when Field1 = 3 then cnt else 0 end),
            Column4 = sum (case when Field1 = 4 then cnt else 0 end)

From (
     Select Field1, cnt = Count(*)
         From Table1
         Group By Field1) as dtable

Quote:> Is there a way that I can run a query, with a group by and return the
values
> in a single record?

> Example

>     This query may return 4 records
>     Select Field1, Count(*)
>         From Table1
>         Group By Field 1

>         Field1:        Count(*)
>            1                  25
>            2                 10
>            3                  23
>            4                    4

> I want to take those results and return a SINGLE record query with a
colume
> for each record
>    Example:
>      Colume Name -->        1        2        3        4
>      Record Values -->       25     10      24       4

> Is there a way to perform this function?

> Any info would be appreciated!

> Thanks
> Lee

BP:

I believe this is what I tried, but I'll try it again to make sure!

Thanks for the information