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

"Lemune" <alfredosilitonga@gmail.com> wrote in message
news:ba89ccb4-48b5-4aab-a736-339292ca7b9f@56g2000hsm.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

Hi

Posting DDL and your sample data as insert statements will make it easier
for people to answer your post

USE TEMPDB
GO
CREATE TABLE AREACounts ( name char(5), subarea char(8), member char(7) )
GO

INSERT INTO AREACounts ( name, subarea, member )
SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member2'
UNION ALL SELECT 'AreaA','SubAreaA','Member3'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member2'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member3'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member2'
UNION ALL SELECT 'AreaA','SubAreaA','Member3'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaA','Member2'
UNION ALL SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT 'AreaA','SubAreaB','Member3'
UNION ALL SELECT 'AreaA','SubAreaB','Member1'
UNION ALL SELECT 'AreaA','SubAreaB','Member1'
UNION ALL SELECT 'AreaA','SubAreaB','Member2'
UNION ALL SELECT 'AreaA','SubAreaB','Member3'
UNION ALL SELECT 'AreaA','SubAreaB','Member1'
UNION ALL SELECT 'AreaB','SubAreaA','Member1'
UNION ALL SELECT 'AreaB','SubAreaB','Member2'
UNION ALL SELECT 'AreaB','SubAreaA','Member1'
UNION ALL SELECT 'AreaB','SubAreaB','Member3'
UNION ALL SELECT 'AreaB','SubAreaA','Member1'

SELECT [name] AS Members_Per_SubArea,
[SubAreaA], [SubAreaB], [SubAreaA] + [SubAreaB] as [total]
FROM
(SELECT DISTINCT [name], SubArea, member
FROM AREACounts
) AS SourceTable
PIVOT
(
COUNT([Member])
FOR SubArea IN ([SubAreaA], [SubAreaB])
) AS PivotTable

To get the total you can either union with the above query as a derived
table although this may be better as a view


CREATE VIEW vw_pivot AS
SELECT [name],
[SubAreaA], [SubAreaB], [SubAreaA] + [SubAreaB] as [total]
FROM
(SELECT DISTINCT [name], SubArea, member
FROM AREACounts
) AS SourceTable
PIVOT
(
COUNT([Member])
FOR SubArea IN ([SubAreaA], [SubAreaB])
) AS PivotTable

This gives:
SELECT [name],
[SubAreaA], [SubAreaB], [total]
FROM vw_pivot
UNION ALL
SELECT 'Total',
SUM([SubAreaA]),
SUM([SubAreaB]),
SUM([total])
FROM vw_pivot

This would not guarantee order but you can add an order column and make
this a derived table

SELECT [name], [SubAreaA], [SubAreaB], [total]
FROM (
SELECT 1 AS srtcol, [name],
[SubAreaA], [SubAreaB], [total]
FROM vw_pivot
UNION ALL
SELECT 2, 'Total',
SUM([SubAreaA]),
SUM([SubAreaB]),
SUM([total])
FROM vw_pivot
) A
ORDER BY srtcol ASC

John