How to convert a column into row in sql query usnig pivot

Question

Using the query select Size,ColorName,QNTY from tbl1.

I'm getting following table

Now I want to get the table in following format

How it possible using pivot

Answer 1

Try this:

SELECT *
FROM (SELECT Size, ColorName, Qnty
      FROM YourTable)p
      PIVOT (SUM(Qnty) FOR Size IN ([34],[32],[30])Pvt

In order to have dynamic pivot use following query:

DECLARE @columns NVARCHAR(1000) = '',
        @sql NVARCHAR(MAX)

SELECT @Columns = STUFF((SELECT ',['+Size+']'
                        FROM (SELECT DISTINCT Size FROM YourTable)z
                        FOR XML PATH('')),1,1,'')


SET @sql = 'SELECT * 
            FROM (SELECT Size, ColorName, Qnty
                  FROM YourTable)p
            PIVOT (SUM(Qnty) FOR Size IN ('+@columns+')Pvt'

EXECUTE(@sql)

Answer 2

Try this

Live Demo

Mathod 1:

SELECT * FROM 
(
  SELECT Size, ColorName, Qnty
  FROM Table1
)p
PIVOT 
(
 Max(Qnty) 
 FOR Size IN ([34],[32],[30])
)Pvt

Method 2:

DECLARE @QuestionList nvarchar(max);
DECLARE @qry nvarchar(max);

SELECT @QuestionList = STUFF((SELECT ',['+ CAST(Size AS nVarchar(max))+']'
                        FROM (SELECT DISTINCT Size FROM Table1)z
                        FOR XML PATH('')),1,1,'')

SET @qry = 'SELECT * FROM 
            (SELECT ColorName, Qnty, Size FROM Table1
            )X PIVOT (MAX(Qnty) FOR Size IN (' + @QuestionList + ')) pvt
            ORDER BY Colorname;';

Exec sp_executesql @qry;

Method 3:

SELECT ColorName,
       MAX(CASE WHEN Size = 34 THEN Qnty END) AS [34],
       MAX(CASE WHEN Size = 32 THEN Qnty END) AS [32],
       MAX(CASE WHEN Size = 30 THEN Qnty END) AS [30]
FROM Table1
GROUP BY ColorName;