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