How do I perform the following multi-layered pivot with TSQL in Access 2010?

Question

I have looked at the following relevant posts:

How to create a PivotTable in Transact/SQL?

SQL Server query - Selecting COUNT(*) with DISTINCT

SQL query to get field value distribution

Desire: The have data change from State #1 to State #2.

Data: This data is a collection of the year(s) in which a person (identified by their PersonID) has been recorded performing a certain activity, at a certain place.

My data currently looks as follows:

State #1

Row | Year | PlaceID | ActivityID | PersonID

001   2011    Park       Read         201a                
002   2011   Library     Read         202b  
003   2012   Library     Read         202b
004   2013   Library     Read         202b
005   2013   Museum      Read         202b
006   2011    Park       Read         203c
006   2010   Library     Read         203c
007   2012   Library     Read         204d 
008   2014   Library     Read         204d

Edit (4/2/2014): I decided that I want State #2 to just be distinct counts.

What I want my data to look like:

State #2               

Row |  PlaceID |  Column1 | Column2 | Column3 

001      Park        2           
002     Library      1         1         1
003     Museum       1

Where:

Column1: The count of the number of people that attended the PlaceID to read on only one year.

Column2: The count of the number of people that attended the PlaceID to read on two different years.

Column3: The count of the number of people that attended the PlaceID to read on three different years.

In the State #2 schema, a person cannot be counted in more than one column for each row (place). If a person reads at a particular place for 2010, 2011, 2012, they appear in Row 001, Column3 only. However, that person can appear in other rows, but once again, in only one column of that row.

My methodology (please correct me if I am doing this wrong):

I believe that the first step is to extract distinct counts of the number of years each person attended the place to perform the activity of interest (please correct me on this methodology if incorrect).

As such, this is where I am with the T-SQL:

SELECT 
PlaceID
,PersonID
,[ActivityID]
,COUNT(DISTINCT [Year]) AS UNIQUE_YEAR_COUNT
FROM (
SELECT
     Year
    ,PlaceID
    ,ActivityID
    ,PersonID
FROM [my].[linkeddatabasetable]
WHERE ActivityID = 'Read') t1
GROUP BY
    PlaceID
    ,PersonID
    ,[ActivityID]
ORDER BY 1,2

Unfortunately, I do not know where to take it from here.

Is State #2 for a particular person? Where does T-SQL/Access come in? Is access set up as a linked server in Sql-Server? or other way around? How this is set up influences what syntax to use for a pivot. — Brad, Apr 02 '14 at 18:55
@Brad:I am using T-SQL in Access because I have an ODBC-linked SQL Server table within my Access 2010 environment that I need to use for this query. I will elaborate, and fix my explanation of State #2. — ealfons1, Apr 02 '14 at 19:12
Is there a mistake in your Stage #2. only 1 person came to read at the library one time, right? — Brad, Apr 02 '14 at 20:29
@Brad:Yes, there was a mistake in my Stage #2 counts. I fixed the error. — ealfons1, Apr 03 '14 at 12:45

score 3 · Accepted Answer · answered Apr 02 '14 at 20:31

I think you have two options.

A traditional pivot

select placeID, 
  , Column1 = [1]
  , Column2 = [2]
  , Column3 = [3]
from 
(
SELECT 
    PlaceID
    ,COUNT(DISTINCT [Yearvalue]) AS UNIQUE_YEAR_COUNT
    FROM (
    SELECT
         yearValue
        ,PlaceID
        ,ActivityID
        ,PersonID
    FROM #SO
    WHERE ActivityID = 'Read') t1
    GROUP BY
        PlaceID
        ,PersonID
        ,[ActivityID]) up
pivot (count(UNIQUE_YEAR_COUNT) for UNIQUE_YEAR_COUNT in ([1],[2],[3]) ) as pvt

or as a case/when style pivot.

select I.PlaceID
    , Column1 = count(case when UNIQUE_YEAR_COUNT = 1 then PersonID else null end)
    , Column2 = count(case when UNIQUE_YEAR_COUNT = 2 then PersonID else null end)
    , Column3 = count(case when UNIQUE_YEAR_COUNT = 3 then PersonID else null end)
from (
    SELECT 
        PlaceID
        , PersonID
        ,COUNT(DISTINCT [Yearvalue]) AS UNIQUE_YEAR_COUNT
    FROM (
    SELECT
         yearValue
        ,PlaceID
        ,ActivityID
        ,PersonID
    FROM #SO
    WHERE ActivityID = 'Read') t1
    GROUP BY
        PlaceID
        ,PersonID
        ,[ActivityID]) I
group by I.PlaceID

score 1 · Answer 2 · answered Apr 02 '14 at 19:46

Since you are in Access I would think using an aggregate functions would do the work.

Try with DCOUNT() to begin with http://office.microsoft.com/en-us/access-help/dcount-function-HA001228817.aspx.

Replace your count() with dcount("year", "linkeddatabasetable", "placeid=" & [placeid])

How do I perform the following multi-layered pivot with TSQL in Access 2010?

2 Answers2