It's all correct now. Thank you very much!






>>Ok, it's time to step back and rethink this.
>>
>>I don't understand the syntax you've been providing, but I do know that what I want to do CANNOT really be this difficult. Let me restate my goals.
>>
>>
>>In table tblCamp_CT I have 2 columns MailCounty and Precinct. There are multiple occurances of each precinct in each county, as in
>>
>>

>>MailCounty     Precinct
>>=============================
>>County A        Precinct 1
>>County A        Precinct 2
>>County A        Precinct 2
>>County A        Precinct 2
>>County A        Precinct 3
>>County B        Precinct 4
>>County B        Precinct 5
>>County B        Precinct 5
>>County B        Precinct 6
>>County C        Precinct 7
>>County D        Precinct 8
>>County D        Precinct 8
>>County D        Precinct 9
>>

>>
>>What I want back is
>>

>>County A      County B     County C      County D     
>>==========================================================
>>Precinct 1    Precinct 4   Precinct 7    Precinct 8
>>Precinct 2    Precinct 5                 Precinct 9 
>>Precinct 3    Precinct 6
>>

>>
>>So, each county should become a column, and each precinct should only appear once under the county it belongs in.
>>
>>So far, all the code you've provided have returned different and inaccurate results. I am grateful for the help, but I can't beleive it's really this difficult. What are we doing wrong here?
>>
>>Thanks
>
>I'm trying to figure out why that code returned wrong results as it should have given the correct results.
>
>Anyway, try:
>

>declare @SQL nvarchar(max), @Cols nvarchar(max)
>
>;with Counties as (select distinct MailCounty from tbl)
>
>select @Cols = STUFF((select ', ' + quotename(MailCounty) from Counties ORDER BY MailCounty FOR XML PATH('')),1,2,'') -- this produces list of counties
>
>set @SQL = 'SELECT ' + @Cols + ' FROM (select MailCounty, Precinct, 
>DENSE_RANK() OVER (PARTITION BY MailCounty ORDER BY Precinct) Rn FROM myTbl ) X -- the inner select with dense_rank should work!
>PIVOT (max(Precinct) FOR MailCounty IN (' + @Cols + ')) pvt'
>
>execute (@SQL)
>

>
>
>It is the same code, but as I'm telling you, it is a correct code. I did try it locally also before with duplicates and it works fine. Unless there is something wrong with the table itself (you can run DBCC CheckTable 'myTable' to verify).
>
>You will see NULLs for counties with less distinct precincts than other counties. If you want to see blank row instead, let me know, as this is easy to change also.
>
>I ran this code 8+ times (so I did create duplicates) and I got the same result every time:
>
>

>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'),
>('Adams',		'WEST ROOT'),  
>('Adams',	'BERNE A'),
>('Adams',	'BERNE B'),
>('Adams',	'DECATUR 1'),
>('Adams',	'DECATUR 2'),
>('Adams',	'GENEVA'),
>('Adams',	'PREBLE')             
>
>
>declare @SQL nvarchar(max), @Cols nvarchar(max)
>
>;with Counties as (select distinct MailCounty from #Counties )
>
>select @Cols = STUFF((select ', ' + quotename(MailCounty) from Counties ORDER BY MailCounty FOR XML PATH('')),1,2,'') -- this produces list of counties
>
>set @SQL = 'SELECT ' + @Cols + ' FROM (select MailCounty, Precinct, 
>DENSE_RANK() OVER (PARTITION BY MailCounty ORDER BY Precinct) Rn FROM #Counties ) X -- the inner select with dense_rank should work!
>PIVOT (max(Precinct) FOR MailCounty IN (' + @Cols + ')) pvt'
>
>execute (@SQL)
>