Pivot\Unpivot issues?

Question

I have the following data

Col1,  Col2,  Col3, Col4, Col5                         
1, P,  W, M, adhfggh                 
1, P,  W, M, fdasdfd                    
1, P,  W, M, retretre

So, I would like to get this

Col1,  Col2,  Col3, Col4, ColA, ColB, ColC         
1, P,  W, M, adhfggh, fdasdfd, retretre

I should try something like this, but not sure what to put in brackets

select Col1, Col2, Col3, Col4, Col5 from tableA         
Unpivot 
( Col1,  Col2,  Col3, Col4 for Col5 in (, , ) ) as UnPvt

I appreciate for any help.

Answer 1

You didn't specify what version of SQL you are using so this is T-SQL and will work on any SQL Server after 2008.

This dynamic pivot was altered from this answer & will format to your requirements. You might also wish to look into the STUFF function

    CREATE TABLE #T
                  (  Col1 int 
                    ,Col2 [nchar](3) 
                    ,Col3 [nchar](3)
                    ,Col4 [nchar](3)
                    ,Col5 [nchar](10)
                    )

    Insert Into #T
    Values
    (1,'P','W','M','adhfggh')
    ,(1,'P','W','M','fdasdfd')
    ,(1,'P','W','M','retretre');

    DECLARE @cols AS NVARCHAR(MAX),
            @query  AS NVARCHAR(MAX);

    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Col5) 
                       FROM #T c
                       FOR XML PATH(''), TYPE
                       ).value('.', 'NVARCHAR(MAX)') 
                       ,1,1,'')

    set @query = 'SELECT Col1,Col2,Col3,Col4, ' + @cols + ' from 
        (
            select  Col1,   
                    Col2,  
                    Col3,
                    Col4,
                    Col5                       
            from #T     
        ) x
        pivot 
        (
             max(Col5)
            for Col5 in (' + @cols + ')
        ) p '


    execute(@query)

    drop table #T