[AccessD] SQL Server: Crosstab (pivot)

I wonder if the hashes are truly unique. Have you tested the results?

Recently, I was working on a client's project but it took a long time to
create an algorithm that generated a hash that did not have too many false
duplicates. The hash needed was about 30 characters but the table record
count was only about 400K...small compared to your files.  

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, August 14, 2012 6:02 AM
To: Access Developers discussion and problem solving; Sqlserver-Dba
Subject: [AccessD] SQL Server: Crosstab (pivot)

I have a general situation where I have three fields in a table:

tblAZData (address data)
HashPerson
HashFamily
HashAddress


HashPerson contains exactly that, a hash of FName, LName, Addr, Zip5, Zip4
HashFamily contains a hash of LName, Addr, Zip5, Zip4
HashAddr contains a hash of Addr, Zip5, Zip4

Assume I have 5 people in the same family at an address, and I have 2 people
from another family at 
that same address.

For those 7 records I would have 7 distinct HashPerson values, 2 distinct
HashFamily values and 1 
distinct HashAddr value.

Now assume a state full of this kind of data.

Now I am going to throw in a Nielsen rating (television viewing data) for
the zip5 code.  Nielsen 
codes are A,B,C and D.

I need to get a cross tab something like:
Nielsen code>	A	B	C	D
HashPerson	8765	5678	4356	9876	
HashFamily	7687	4643	4076	9074
HashAddr	7009	4543	3987	8823

I generate this data "manually" by:

1) Building a base query that selects the general population to be counted

2) Building three queries based on the base query - HashPerson_Distinct,
HashFamily_Distinct and 
HashAddr_Distinct

3) Building three queries - HashPerson_Distinct_Cnt, HashFamily_Distinct_Cnt
and HashAddr_Distinct_Cnt

4) Running these last three queries and then pasting them into a
spreadsheet.  Unfortunately the 
results end up:

Nielsen	Person	Family	Address
A
B
C
D

So I am trying to make this easier.  I am doing this kind of stuff a LOT in
a lot of different 
circumstances.  However the base information, a selection of records
containing HashPerson, 
HashFamily and HashAddress for which I have to get distinct values for each
of those and counts for 
each of those.

I think I could figure out a simple pivot if it were a simple count, however
the fact that I have to 
do a distinct and then a count is throwing a wrench in my ability to handle
the while thing.

Is this something that can be solved in a general way in TSQL or am I just
stuck doing what I am doing?

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com