 | |
Pivot / Crosstab With Count Unique data.
comp.databases.theory comp.databases.theory
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Pivot / Crosstab With Count Unique data. -
07-17-2008
, 12:55 AM
#2
| |||
| |||
|
|
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 |
(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
#3
| |||
| |||
|
|
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 |
(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
#4
| |||
| |||
|
|
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 |
(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
#5
| |||
| |||
|
|
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 |
(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
#6
| |||
| |||
|
|
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 |
(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
#7
| |||
| |||
|
|
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 |
(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
#8
| |||
| |||
|
|
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 |
(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
#9
| |||
| |||
|
|
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 |
(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
#10
| |||
| |||
|
|
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 |
(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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.