Here's something to get you started:declare @t table (ColA char(1), ColB int)insert into @t (ColA, ColB) values('A', 12),('B', 22),('C', 45),('A', 56),('A', 34),('B', 23)select A0, B0, C0, A1, B1, C1, A2, B2, C2from ( select colA + cast(-1+ROW_NUMBER() over(partition by colA order by colA) as char(1)) as ColA , t.ColB from @t t ) srcpivot (max(colB) for colA in (A0, B0, C0, A1, B1, C1, A2, B2, C2))pvt
Note that your input table does not have any sequence information. You cannot assume that SQL will return rows from your table in insertion order, since a table is a set and therefore unordered by definition. I assigned row numbers to get around this problem, but that doesn't mean that the row numbers will be assigned in insertion order either.BTW this solution is not very scalable. What if you had 1000 rows for 'A'? 10000 rows? a million rows?SQL has a limit of 4096 columns per query: http://msdn.microsoft.com/en-us/library/ms143432.aspxAlso, if you want to eliminate the Null columns in the result of the query above, you can do it with dynamic SQL.