Pivot / Crosstab With Count Unique data.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lemune - 17 Jul 2008 06:55 GMT

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

Reply to this Message

David BL - 17 Jul 2008 07:40 GMT

> Hi,
>
[quoted text clipped - 52 lines]
>
> Thanks in advanced

I think 4 different select queries are needed

(2x2 submatrix in top left)
SELECT area, subarea, COUNT(DISTINCT member) FROM mytable

(1x2 submatrix in bottom left)
SELECT subarea, COUNT(DISTINCT member) FROM mytable

(2x1 submatrix in top right)
SELECT area, COUNT(DISTINCT member) FROM mytable

(1x1 submatrix in bottom right)
SELECT COUNT(DISTINCT member) FROM mytable

Reply to this Message

Lemune - 17 Jul 2008 08:47 GMT

Thank for your quick reply David.

Could we do it in dynamic, what about if i want to add the field,
etc?

Thanks

Reply to this Message

David Cressey - 17 Jul 2008 12:33 GMT

> Hi,
>
[quoted text clipped - 52 lines]
>
> 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.

Reply to this Message

TroyK - 17 Jul 2008 20:42 GMT

> > Hi,
>
[quoted text clipped - 63 lines]
>
> - 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

Reply to this Message