Help with PIVOT

Question

I have a table as follows

Name        |          Words
A              words for A1 here
B              words for B1 here
C               words for C1 here
A               words for A2 here
B               words for B2 here
C               words for C2 here

I want to pivot the above table to get the following result

A                    |      B                 |       C
words for A1 here       words for B1 here         words for C1 here
words for A2 here       words for B2 here         words for C2 here

Thanks

Answer 1

select A, B, C from
(
  select Name, CAST(Words as nvarchar(1000)) as Words from DemoTable
) up
pivot (Max(words) for Name in (A, B, C)) as pvt

Answer 2

With Numbered as
(
select *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Words) AS RowNum
from yourTable)
select [A],[B],[C]
from Numbered n
pivot (max(Words) for Name in ([A],[B],[C])) p
;