>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 >>
>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 >>
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).
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)