SQL SERVER-PIVOT用法 [英] SQL SERVER - PIVOT usage

问题描述

你好，

我有一个名为T1的表，该表的一列中有数据.我必须将该列数据转换为其他表的行数据.例如...

Hello,

I have a table named T1 which has data in a column. I have to make that column data to a row data for other table. For example...

ID      Salary

1	 48
2	 80
3	 50
4	 70
5	 85

我还有其他5列A1至A5的表格T2.我必须从T1到T2插入值，例如...

I have other table T2 with 5 columns A1 to A5. I have to insert the values from T1 to T2 like...

A1  A2  A3  A4  A5
48  80  50  70  85

听说我可以使用UNPIVOT和PIVOT.但是我无法得到我想要的.

有人可以请我欺骗吗?


谢谢，
Vasini

Heard I can use UNPIVOT and PIVOT. But I am not able to get what I want.

Can anyone please giude me on this?


Thanks,
Vasini

推荐答案

尝试以下操作:

SELECT [1] AS A1, [2] ASD A2, [3] AS A3, [4] AS A4, [5] AS A5
FROM
(
    SELECT ID, Salary
    FROM SalaryTable
) AS SourceTable
PIVOT
(
    SUM(Salary)
    FOR ID IN ([1], [2], [3], [4], [5])
) AS PivotTable

这是我第一次尝试使用 PIVOT [

This is my first attempt using PIVOT[^] so I hope it is correct.

那里可能是一种更好的方法-我只是开始研究PIVOT-但这可行:

There may well be a better way to do this - I''m only just starting to look at PIVOT - but this works:

SELECT * FROM T1
PIVOT(MAX(SALARY) FOR ID IN ([1],[2], [3], [4], [5])) p

最后的"p"是必需的(枢轴需要一个名称，即使您不再引用它也是如此)，并且需要MAX，因为PIVOT需要聚合函数.

The ''p'' at the end is necessary (the pivot needs a name, even if you don''t refer to it again) and the MAX is needed because PIVOT requires an aggregate function.

Solution2完美运行.谢谢.

Hi,

Solution2 worked perfectly. Thank you.

这篇关于SQL SERVER-PIVOT用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！