Sql query for Transpose of a dynamic Table

Question

The sql table is as follows,

    Name    Salary   NoticePeriod   CTC
    Jack     1520       15          123
    Bruce    1423       35          165

and it contains about 1000 rows.

I need to do a transpose of this table ,such that the expected output is

    Fields           Jack     Bruce   Mike ..... Sam
    Salary           1520     1423    235   ..   561
    NoticePeriod      15       35      23        253 
    CTC               123      165     45   ...  125

I tried using Pivot and Unpivot function in Sql Server 2008 . But Since the Name record is large , Pivot query doesnt helps.

My sql attempt is follows,

SELECT *
FROM (
  SELECT NAME,
    Salary,
    NoticePeriod,
    CTC
  FROM CTCTABLE WITH (NOLOCK)
  ) AS queryTable
UNPIVOT(Value FOR NAME IN (NAME, Salary, NoticePeriod, CTC)) AS unpv
PIvot(max(Value) FOR NAME IN (Salary, NoticePeriod, CTC)) pv

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Evaldas Buinauskas, Oct 20 '15 at 11:21

Mahmoud Gamal · Accepted Answer · 2015-10-20T11:18:06.637

In your case, you should unpivot the columns Salary, NoticePeriod, CTC into rows, then PIVOT:

WITH Unpivoted
AS
(
    SELECT Name, Fields, SalaryValue
    FROM salaries AS s
    UNPIVOT
    (
      SalaryValue
      FOR  Fields IN(Salary, NoticePeriod, CTC)
    ) AS u
)
SELECT Fields, jack,bruce
FROM Unpivoted AS u
PIVOT
(
  MAX(SalaryValue)
  FOR Name IN(Jack, Bruce)
) AS p;

The UNPIVOT will transform the columns Salary, NoticePeriod, CTC into values:

Then the pivot will pivot the salary values for each field value and transform the names into columns.

And of course you have to do it dynamically instead of writing list of names like this:

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

SELECT @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(name)
                       FROM salaries
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');


SELECT @query = 'WITH Unpivoted
                AS
                (
                    SELECT Name, Fields, SalaryValue
                    FROM salaries AS s
                    UNPIVOT
                    (
                        SalaryValue
                        FOR  Fields IN(Salary, NoticePeriod, CTC)
                    ) AS u
                )
                SELECT Fields, ' + @cols + '
                FROM Unpivoted AS u
                PIVOT
                (
                    MAX(SalaryValue)
                    FOR Name IN(' + @cols + ')' +
                ') p';

execute(@query);

This will give you:

Live Demo (Thanks to @lad2025 for it)

Just in case if anyone wants to play with it https://data.stackexchange.com/stackoverflow/query/377813 — Lukasz Szozda, Oct 20 '15 at 11:16
Thanks all for your efforts. But Jack was the first record inserted to the base table. When Pivot and Unpivot has been done , Bruce column came first and Jack came. Cant we rearrange the columns based on the table record insertion order. — Cheral Irumborai, Oct 21 '15 at 02:02
@CheralIrumborai - In SQL Server and most RDBMS, the order of the rows can't be granted and it is in significant. But in your case if you want to order by the inserted values you can just remove the `distinct` from the variable: `SELECT @cols = STUFF((SELECT ',' + QUOTENAME(name) FROM salaries FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') , 1, 1, '');` this way `jack` will be first, but I recommend you specify an order by clause. — Mahmoud Gamal, Oct 21 '15 at 08:59

Sql query for Transpose of a dynamic Table

1 Answers1

Linked