Re: Pivot / Crosstab With Count Unique data.

On Jul 17, 5:33 am, "David Cressey" <cresse..._at_verizon.net> wrote:
> "Lemune" <alfredosilito..._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.- Hide quoted text -
>
> - Show quoted text -

SQL Server introduced "PIVOT" and "UNPIVOT" operators in v2005. Syntactically a bit cleaner than a "MAX(...) ... GROUP BY..." expression, but the latter is, actually, a bit better in terms of processor utilization.

Since you need to specify attribute values that become columns, the only way (in T-SQL at least) to dynamically extend the column values returned in the resultset is to use dynamic SQL.

TroyK Received on Thu Jul 17 2008 - 21:42:07 CEST