dynamic pivot table with duplicate column name

Question

I have the following table:

Name       |       Value
---------------------------
key1       |       val1
key2       |       val2
key1       |       val3
key4       |       val4
.          |       .
.          |       .
.          |       .
.          |       .

I need the output to be as follows:

key1       |    key2       |    key1       |    key4      |    ......
-----------------------------------------------------------------------------
val1       |    val2       |    val3       |    val4      |    ...... 

I have tried achieving this using dynamic pivot, but it fails because the key1 is a duplicate column.

SQLError: The column 'key1' was specified multiple times for 'p'.

Pivot Query that I used: http://sqlfiddle.com/#!3/33f66/4

Answer 1

This is probably not possible using a pivot as the column names are identical. Here is another solution:

DECLARE @sql AS NVARCHAR(MAX)

SELECT @sql = STUFF((SELECT ',''' + value + ''' as ' + QUOTENAME(Name)
                    from mytable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

EXEC ('select '+ @sql)

Result:

key1    key2    key1    key4
val1    val2    val3    val4