Crosstab, Pivot?

7 Sep 2006 6:30 PM

dev648237923

I have a table that has two types of results for each part:

part, type, result
---- ---- ------
a     1     1
a     1     4
a     1     5
a     2     4
a     2     7
b    1      1
b    2      5
....

I need to produce a result set that takes the averages for each part's types
(so I have a col for type1 and a col for type2):
part avetype1 avetype2
a     3.33        5.5
b     1             5

Thanks for any info on this!

7 Sep 2006 6:41 PM

Anith Sen

Do:

SELECT part,
       AVG( CASE WHEN type = 1
                 THEN result * 1.
            END ) AS "avetype1",
       AVG( CASE WHEN type = 2
                 THEN result * 1.
            END ) AS "avetype2"
FROM tbl
GROUP BY part ;

--
Anith

7 Sep 2006 7:11 PM

dev648237923

Wow that was great! Applied it to my situation and worked great -- thanks!

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:%23hvfq0q0GHA.4408@TK2MSFTNGP05.phx.gbl...
> Do:
>
> SELECT part,
>       AVG( CASE WHEN type = 1
>                 THEN result * 1.
>            END ) AS "avetype1",
>       AVG( CASE WHEN type = 2
>                 THEN result * 1.
>            END ) AS "avetype2"
> FROM tbl
> GROUP BY part ;
>
> --
> Anith
>

7 Sep 2006 7:33 PM

dev648237923

One thing I was wondering...
Is the * 1. required? I tried with and without and seems to work OK
Thank you
(p.s. my result field is numeric in the db even though my posted question
had ints in them)
Thank you!

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:%23hvfq0q0GHA.4408@TK2MSFTNGP05.phx.gbl...
> Do:
>
> SELECT part,
>       AVG( CASE WHEN type = 1
>                 THEN result * 1.
>            END ) AS "avetype1",
>       AVG( CASE WHEN type = 2
>                 THEN result * 1.
>            END ) AS "avetype2"
> FROM tbl
> GROUP BY part ;
>
> --
> Anith
>

7 Sep 2006 7:52 PM

Anith Sen

For integer values, it might return integer results. The sample data you
posted had non-decimal data, the 1.0 was added to get the average value with
decimals. Alternatively you can use CAST or CONVERT function to change the
datatype as well.

--
Anith

8 Sep 2006 6:55 PM

dev648237923

Earlier Anith Sen gave help (thanks!) on a similar problem to my below one
involving averages --
Now I am wondering how to accomplish similar but instead of averages I have
2 entries per part and I want to have them in a single row (one field for
type1 one for type2)

part, type, result
---- ---- ------
a    1     1
a    2     4
b    1     1
b    2     5
c     1     8
c     2     9

I need...
part, type1result, type2result
---- ---- ------
a, 1, 4
b, 1, 5
c, 8, 9

Thanks!

8 Sep 2006 7:01 PM

Anith Sen

Use an extrema function like MAX() or MIN() instead of AVG()

--
Anith

8 Sep 2006 7:16 PM

dev648237923

Sorry I did not put the correct example before -- I forgot to say that I
have one more level of part info so it would be like this:

top, part, type, result
-------------------
1   a    1     1
1   a    2     4
2   a    1     6
2   a    2     7
3   b    1     1
3   b    2     5
4   c     1     8
4   c     2     9

and I'd need:
part, type1result, type2result
---- ---- ------
1 a, 1, 4
2 a, 6, 7
3 b, 1, 5
4 c, 8, 9

Thank you!

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:OfJU4k30GHA.2636@TK2MSFTNGP06.phx.gbl...
> Use an extrema function like MAX() or MIN() instead of AVG()
>
> --
> Anith
>

9 Sep 2006 9:52 PM

Hugo Kornelis

On Fri, 8 Sep 2006 15:16:02 -0400, dev648237923 wrote:

Show quote

>Sorry I did not put the correct example before -- I forgot to say that I
>have one more level of part info so it would be like this:
>
>top, part, type, result
>-------------------
> 1   a    1     1
> 1   a    2     4
> 2   a    1     6
> 2   a    2     7
> 3   b    1     1
> 3   b    2     5
> 4   c     1     8
> 4   c     2     9
>
>and I'd need:
>part, type1result, type2result
> ---- ---- ------
>1 a, 1, 4
>2 a, 6, 7
>3 b, 1, 5
>4 c, 8, 9
>
>Thank you!

Hi dev648237923,

That doesn't make a big difference - the query can stay almost
unchanged:

SELECT top, part,
       MAX( CASE WHEN type = 1
                 THEN result
            END ) AS "type1result",
       MAX( CASE WHEN type = 2
                 THEN result
            END ) AS "type2result"
FROM tbl
GROUP BY top, part ;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

11 Sep 2006 3:47 PM

dev648237923

Both yours and Aniths ideas worked out great -- thanks!

Show quote

"Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message
news:7qd6g25hdppqkg6adjm8f1206fh2f65dkn@4ax.com...
> On Fri, 8 Sep 2006 15:16:02 -0400, dev648237923 wrote:
>
>>Sorry I did not put the correct example before -- I forgot to say that I
>>have one more level of part info so it would be like this:
>>
>>top, part, type, result
>>-------------------
>> 1   a    1     1
>> 1   a    2     4
>> 2   a    1     6
>> 2   a    2     7
>> 3   b    1     1
>> 3   b    2     5
>> 4   c     1     8
>> 4   c     2     9
>>
>>and I'd need:
>>part, type1result, type2result
>> ---- ---- ------
>>1 a, 1, 4
>>2 a, 6, 7
>>3 b, 1, 5
>>4 c, 8, 9
>>
>>Thank you!
>
> Hi dev648237923,
>
> That doesn't make a big difference - the query can stay almost
> unchanged:
>
> SELECT top, part,
>       MAX( CASE WHEN type = 1
>                 THEN result
>            END ) AS "type1result",
>       MAX( CASE WHEN type = 2
>                 THEN result
>            END ) AS "type2result"
> FROM tbl
> GROUP BY top, part ;
>
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>
> --
> Hugo Kornelis, SQL Server MVP