Naomi,

This worked great. I have never done a pivot before so I learned something.

One thing though.. The results in each column need to be distinct. For example, the result column Adams has:

Adams
---------------------------
DECATUR 5                                         
NULL
NULL
NULL
SO MONROE                                         
BERNE B                                           
BERNE B                                           
BERNE A                                           
BERNE B                                           
BERNE B                                           
SO MONROE                                         
SO MONROE                                         
BERNE C                                           

where it should have

Adams
---------------------------
BERNE A                                           
BERNE B                                           
DECATUR 5                                         
SO MONROE                                         

How can I do this?


>>I have never done a cross tab query (or pivot?), so I could use some help,. using SQL 208.
>>
>>I have a table called tblCamt_CT with columns "MailCounty" and "Precinct".
>>
>>

>>
>>MailCounty	Precinct
>>----------------------------
>>Hancock		WAR 035
>>Hancock		WAR 030
>>Hamilton		SPRING FARMS
>>Johnson		WHITE RIVER 07
>>Cass		Logansport 02
>>Hamilton		NOBLESVILLE 09
>>Hamilton		BRENTWOOD
>>Hamilton		LAW 007
>>Hancock		Brandywine 1
>>Adams		WEST ROOT
>>Adams		WEST ROOT                                    
>>

>>
>>I want to create a result set with one column per county and a distinct list of precincts under each county, So I should end up with somethign like:
>>
>>

>>
>>Adams		Cass		Hancock		Hamilton		Johnson
>>==================================================================================
>>WEST ROOT	Logansport 02	Brandywine 1	BRENTWOOD	WHITE RIVER 07
>>						WAR 030		SPRING FARMS				
>>						WAR 035		NOBLESVILLE 09
>>						LAW 007
>>
>>

>>
>>I'v never done a cross tab before. Could use some help.
>>
>>Thanks
>
>It's a bit tricky, but here it goes with dynamic pivot.
>
>

>use tempdb
>create table #Counties (MailCounty varchar(30), Precinct varchar(30))
>
>insert into #Counties 
>values
>('Hancock',		'WAR 035'),
>('Hancock',		'WAR 030'),
>('Hamilton',	'SPRING FARMS'),
>('Johnson',		'WHITE RIVER 07'),
>('Cass',		'Logansport 02'),
>('Hamilton',	'NOBLESVILLE 09'),
>('Hamilton',	'BRENTWOOD'),
>('Hamilton',	'LAW 007'),
>('Hancock',		'Brandywine 1'),
>('Adams',		'WEST ROOT'),
>('Adams',		'WEST ROOT')  
>
>declare @Cols nvarchar(max), @SQL nvarchar(max)
>
>select @Cols = STUFF((select distinct ', ' + quotename(MailCounty) from #Counties order by ', ' + quotename(MailCounty) 
>for XML path('')),1,2,'')
>
>set @SQL = 'select ' + @Cols + ' from (select *, DENSE_RANK() over (partition by MailCounty order by Prestinct) as Rn 
>from #Counties) X PIVOT (max(Precinct) FOR MailCounty IN (' + @Cols + ')) pvt'
>
>print @SQL -- to debug
>
>execute (@SQL)