Pivot in sql for multiple columns

Question

I have a table as following:

Using pivot I need the following output:

The query should not use Union set operator.

Answer 1

You could first do UNPIVOT to turn data in the following format:

and then do PIVOT by the Type column:

This is full working example:

DECLARE @DataSource TABLE
(
    [CatDes] CHAR(1)
   ,[val1] TINYINT
   ,[val2] TINYINT
);

INSERT INTO @DataSource ([CatDes], [val1], [val2])
VALUES ('a', 1 ,2)
      ,('b', 3 ,4)
      ,('c', 5 ,6)
      ,('d', 7 ,8);

SELECT *
FROM @DataSource
UNPIVOT
(
    [Value] FOR [Type] IN ([val1], [val2])
) UNPVT
PIVOT
(
    MAX([Value]) FOR [CatDes] IN ([a], [b], [c], [d])
) PVT

Of course, when you are using PIVOT and UNPIVOT you should hard-coded the column values. I guess, in your real case, this is not an option, so I will recommend to you to use a dynamic T-SQL statement - build the statement as a string and then execute it using sp_executesql.