Re: Pivot / Crosstab With Count Unique data.

From: David Cressey <cressey73_at_verizon.net>
Date: Thu, 17 Jul 2008 11:33:04 GMT
Message-ID: <Q5Gfk.366$Cw5.305_at_trnddc01>


"Lemune" <alfredosilitonga_at_gmail.com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea_at_y38g2000hsy.googlegroups.com...
> Hi,
>
> I'm trying to create pivot, my data are area, sub area, member code.
> On Pivot/Crosstab data area i want to calculate how many member that
> access in sub area, and how many member that access in area, where as
> on the raw data it self my member has many record on each area and sub
> area. My be this will make an sample
>
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member3
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member3
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member3
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member1
> * AreaA SubAreaB Member3
> * AreaA SubAreaB Member1
> * AreaA SubAreaB Member1
> * AreaA SubAreaB Member2
> * AreaA SubAreaB Member3
> * AreaA SubAreaB Member1
> * AreaB SubAreaA Member1
> * AreaB SubAreaB Member2
> * AreaB SubAreaA Member1
> * AreaB SubAreaB Member3
> * AreaB SubAreaA Member1
>
> The result of my pivot is That I want:
>
> Sub Area
>
> Area SubAreaA SubAreaB Total
>
> AreaA 3
> 3 3
>
> AreaB 1
> 2 3
>
> Total 3
> 2 3
>
> Could we create this kind of pivot?
>
> If it could be done, how we do it?
>
> Thanks in advanced

Some database tools, like MS Access, have crosstab queries built in. What you want to do is very straightforward with a crosstab query.

If you are working with Oracle, there's an article in the complete reference to Oracle entitled "Turning a table on its side." This describes how to make a crosstab query using "select".

I'm not sure about SQL server or other tools. Received on Thu Jul 17 2008 - 13:33:04 CEST

Original text of this message