SQL查询动态表的转置 [英] Sql query for Transpose of a dynamic Table

问题描述

sql表如下，

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

它包含大约1000行.

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

我尝试在Sql Server 2008中使用Pivot和Unpivot函数.但是，由于名称"记录很大，因此数据透视查询无济于事.

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

我的sql尝试如下，

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

推荐答案

在您的情况下，应先将Salary, NoticePeriod, CTC列分解为行，然后将PIVOT分解为行:

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;

UNPIVOT会将Salary, NoticePeriod, CTC列转换为值:

然后，数据透视表将对每个字段值的薪水值进行透视，并将名称转换为列.

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);

这将为您提供:

• 实时演示(感谢@ lad2025)

• Live Demo (Thanks to @lad2025 for it)

这篇关于SQL查询动态表的转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！