CODEWITHSUNDEEP
sqlsql-serverpivot
Vignesh Paramasivam
Vignesh Paramasivam

Reputation: 2460

sql table row - column conversion

I have following table:

enter image description here

How to convert the above table into below structure? I tried using pivot table but couldn't get it to work.

enter image description here

Upvotes: 0

Views: 411

Answers (1)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

You need to have to look up SQL PIVOT.

Check this fiddle

And the code:

SELECT    * 
FROM 
(
    SELECT    Prodname, 
              pcode, 
              Biiledamt
    FROM      Product
) p
PIVOT 
(
    SUM    (Biiledamt)
    FOR    Prodname IN ([Prod1],[Prod2],[Prod3],[Prod4])
)   AS pvt

If you do not know beforehand the columns then you can check this fiddle which dynamically generates the columns to use.

The code for that is:

DECLARE @cols AS VARCHAR(MAX),
        @query AS VARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',[' + Prodname +']'
            FROM Product c
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')


SET @query = 
'    SELECT    * 
    FROM 
    (
        SELECT    Prodname, 
                  pcode, 
                  Biiledamt
        FROM      Product
    ) p
    PIVOT 
    (
        SUM    (Biiledamt)
        FOR    Prodname IN (' + @cols + ')
    )   AS pvt
'


EXEC(@query)

Upvotes: 1

Related Questions