[dba-SQLServer] SQL Server: Crosstab (pivot)

jwcolby jwcolby at colbyconsulting.com
Tue Aug 14 08:02:18 CDT 2012


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



More information about the dba-SQLServer mailing list