 | |
Pivot / Crosstab With Count Unique data.
comp.databases.theory comp.databases.theory
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: Pivot / Crosstab With Count Unique data. -
07-17-2008
, 02:42 PM
|
"Lemune" <alfredosilito... (AT) gmail (DOT) com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea (AT) y38g2000hsy (DOT) 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 - |
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
#32
| |||
| |||
|
|
"Lemune" <alfredosilito... (AT) gmail (DOT) com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea (AT) y38g2000hsy (DOT) 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 - |
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
#33
| |||
| |||
|
|
"Lemune" <alfredosilito... (AT) gmail (DOT) com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea (AT) y38g2000hsy (DOT) 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 - |
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
#34
| |||
| |||
|
|
"Lemune" <alfredosilito... (AT) gmail (DOT) com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea (AT) y38g2000hsy (DOT) 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 - |
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
#35
| |||
| |||
|
|
"Lemune" <alfredosilito... (AT) gmail (DOT) com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea (AT) y38g2000hsy (DOT) 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 - |
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
#36
| |||
| |||
|
|
"Lemune" <alfredosilito... (AT) gmail (DOT) com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea (AT) y38g2000hsy (DOT) 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 - |
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
#37
| |||
| |||
|
|
"Lemune" <alfredosilito... (AT) gmail (DOT) com> wrote in message news:865cae5a-6c05-42b6-a471-7c976571ffea (AT) y38g2000hsy (DOT) 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 - |
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
|
« 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.