sql如何实现行转列,举几个例子
时间: 2023-07-20 19:35:16 浏览: 73
SQL中实现行转列可以使用PIVOT操作符,举个例子,假设有以下表格:
```
ID | Name | Category | Value
-----------------------------
1 | A | Cat1 | 10
2 | A | Cat2 | 20
3 | B | Cat1 | 30
4 | B | Cat2 | 40
```
我们可以使用以下SQL语句将Category列转换成多个列:
```
SELECT Name, [Cat1], [Cat2]
FROM (
SELECT Name, Category, Value
FROM table_name
) AS SourceTable
PIVOT (
SUM(Value)
FOR Category IN ([Cat1], [Cat2])
) AS PivotTable;
```
转换后的结果如下:
```
Name | Cat1 | Cat2
------------------
A | 10 | 20
B | 30 | 40
```
另外一个例子,假设有以下表格:
```
ID | Fruit | Price
------------------
1 | Apple | 10
2 | Orange| 20
3 | Banana| 30
```
我们可以使用以下SQL语句将Fruit列转换成多个列:
```
SELECT MAX(CASE WHEN Fruit = 'Apple' THEN Price END) AS Apple,
MAX(CASE WHEN Fruit = 'Orange' THEN Price END) AS Orange,
MAX(CASE WHEN Fruit = 'Banana' THEN Price END) AS Banana
FROM table_name;
```
转换后的结果如下:
```
Apple | Orange| Banana
----------------------
10 | 20 | 30
```