SQL实现数据透视效果_sql透视表函数

一、透视

在SQL中实现类似Excel中数据透视表的效果，需要使用到PiVOT函数

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
    FOR
    [<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

其中， 是不需要进行透视的列，[first pivoted column]、[second pivoted column] 等是需要进行透视的列， 是透视后新表格的列名， 是聚合函数，用于将多行数据聚合成一行数据， 是需要进行聚合的列，[] 是包含新列名的列名，IN 后面的括号中是新表格的列名。
实例数据

用透视函数，把日期打标的枚举值放在列上

select * from 

  (SELECT  
CASE WHEN DATEDIFF(DAY,日期,GETDATE())=1 THEN 'T-1' 
 WHEN DATEDIFF(DAY,发货日期,GETDATE())=2 THEN 'T-2' 
 WHEN DATEDIFF(DAY,发货日期,GETDATE())=3 THEN 'T-3'  END AS 日期打标
,[客户名称]
,[订单量]
  FROM [XX表]
) a 
  
  PIVOT(sum(订单量) for [日期打标] in([T-1],[T-2],[T-3])) as p 
1
2
3
4
5
6
7
8
9
10
11
12

2、动态列名

如果是我们希望动态的设置列名，列名取自某一列的枚举值（去重），可以拼接一个动态查询语句

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

---拼接查询语句-----
SELECT @cols = STUFF(
    (
        SELECT DISTINCT ', [' + 日期 + ']'
        FROM [XX表]
        ORDER BY 1 --ORDER BY 1 表示按照查询结果中的第一列进行排序---
        FOR XML PATH('')--拼接查询结果
    ), 1, 2, ''-------将拼接结果最前面的第一个‘，’替换为''（删除最前面的逗号和空格）
);
select @cols

----定义一个查询，把列名套进去
SET @query = '
SELECT *
FROM (
    SELECT DATEPART(year, date_column) AS sales_year, DATEPART(month, date_column) AS sales_month, sales_amount
    FROM Sales
) AS sales_data
---使用透视函数----
PIVOT (
    SUM(sales_amount)
    FOR sales_month IN (' + @cols + ')
) AS pivot_table
ORDER BY sales_year;
';

EXECUTE(@query);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

上面拼接过程用到两个函数

1. STUFF函数：
   该函数的作用是替换指定字符串的一部分。其语法为：

SELECT STUFF( string_expression, start, length, replacement_string )
1

其中，string_expression是要替换的字符串，start是替换的起始位置，length是要替换的长度，replacement_string是替换后的新字符串。
以下是一个简单的示例：
假设有一个字符串 “Hello, World!”，我们想要将其中的 “World” 替换成 “Universe”，可以使用 STUFF 函数来实现：

SELECT STUFF('Hello, World!', 8, 5, 'Universe')
1

该语句的执行结果为 “Hello, Universe!”，其中，8 表示要替换的起始位置，5 表示要替换的长度，‘Universe’ 是替换后的新字符串。

2. FOR XML PATH(’ ')函数：
   该函数的作用是将查询结果转化为XML格式，并将每一行的结果用拼接成一个字符串。其语法为：

SELECT column_name
FROM table_name
FOR XML PATH('')
1
2
3

其中，column_name是要拼接的列名，table_name是要查询的表名，PATH(‘’)表示不指定XML路径，直接将结果拼接成一个字符串。

这两个函数在SQL查询中非常有用，常用于将查询结果按照一定的格式进行拼接和替换。在上面的例子中，STUFF函数和FOR XML PATH(‘’)函数一起使用，将查询结果中的日期列取出来，按照一定的格式拼接成一个字符串，并赋值给变量@cols。

3、逆透视

UNPIVOT逆透视的作用和透视相反，用法相似，这里就不多赘述了。