时间戳

首页 > 解决方案 > SQL 数据透视表的奇怪用例

sql - SQL 数据透视表的奇怪用例

问题描述

我不认为 SQL Pivot 表可以解决我的问题，但它是我现在最好的猜测解决方案。我不是 SQL 专家，所以我所有的选择都只是通过研究

我使用的一个软件每天都会向 Microsoft SQL 输出一份报告，其中包含大约 35,000 行数据，所有这些行都包含有关我使用的应用程序的不同统计信息。现在虽然我不需要每一行的数据点，但可能有 110 行数据我需要这些信息。

我目前获得的列在随附的屏幕截图中

  1. 数据_id
  2. 报告ID
  3. data_point_group_key
  4. 数据点
  5. 数据点值

当前样本： +---------+-----------+----------------------+------------+-----------------+ | Data_id | report_id | data_point_group_key | data_point | data_point_value| +---------+-----------+----------------------+------------+-----------------+ | 1 | 517 | 111 | Table Name | Work_state | +---------+-----------+----------------------+------------+-----------------+ | 2 | 517 | 111 | # Rows | 3 | +---------+-----------+----------------------+------------+-----------------+ | 3 | 517 | 111 | Size | 100 | +---------+-----------+----------------------+------------+-----------------+ | 4 | 517 | 222 | Mode Name | New App | +---------+-----------+----------------------+------------+-----------------+ | 5 | 517 | 333 | Total Size | 5000000 | +---------+-----------+----------------------+------------+-----------------+ | 6 | 517 | 444 | Table Name | Offline Users | +---------+-----------+----------------------+------------+-----------------+ | 7 | 517 | 444 | # Rows | 100 | +---------+-----------+----------------------+------------+-----------------+ | 8 | 517 | 444 | Size | 500 | +---------+-----------+----------------------+------------+-----------------+ | 9 | 517 | 555 | URI | C:\File1 | +---------+-----------+----------------------+------------+-----------------+

我必须将所有这些数据移动到需要通过列而不是行来获取数据的辅助工具中。

我从一开始就输入 110 行没有问题，但我正试图从长远来看使这个过程自动化。

我从这个 SQL 命令开始转换，它不能完全工作，但我认为这是一个开始

SELECT * FROM
(
SELECT [data_id],
       [report_id],
       [data_point],
       [data_point_value]
FROM dbo.tblReportData
)  AS SourceTable PIVOT(MAX([report_id]) FOR [data_point_value] IN([Default Time Zone],
                   [Version],
                   [File Path],
                   [# of Active Data Feeds],
                   [Log Path])) AS PivotTable;

期望的输出 +---------+-----------+------------+-----------+-----+-----------+--------------+-------+ | Data_id | report_id | Table Name | # of Rows | Size| Mode Name | Total Size | URI | +---------+-----------+------------+-----------+-----+-----------+--------------+-------+ | 1 | 517 | Work State| 3 | 100 | NULL | NULL | NULL | +---------+-----------+------------+-----------+-----+-----------+--------------+-------+ | 2 | 517 | NULL | NULL | NULL| New App | NULL | NULL | +---------+-----------+------------+-----------+-----+-----------+--------------+-------+ | 3 | 517 | NULL | NULL | NULL| NULL | 500000 | NULL | +---------+-----------+------------+-----------+-----+-----------+--------------+-------+ | 4 | 517 |Offline user| 100 | 500 | NULL | NULL | NULL | +---------+-----------+------------+-----------+-----+-----------+--------------+-------+ | 5 | 517 | NULL | NULL | NULL| NULL | NULL |C:\File| +---------+-----------+------------+-----------+-----+-----------+--------------+-------+

标签： sqlsql-serverpivot-table

解决方案

您可以尝试使用CASE WHEN聚合MAX 函数来制作您的数据透视表。

测试DLL

CREATE TABLE tblReportData(
   Data_id INT,
   report_id INT,
   data_point_group_key INT,
   data_point VARCHAR(50),
   data_point_value VARCHAR(50)
);

INSERT INTO tblReportData VALUES ( 1,517 , 111 ,'Table Name','Work_state');
INSERT INTO tblReportData VALUES ( 2,517 , 111 ,'# Rows','3');
INSERT INTO tblReportData VALUES ( 3,517 , 111 ,'Size','100');
INSERT INTO tblReportData VALUES ( 4,517 , 222 ,'Mode Name','New App');
INSERT INTO tblReportData VALUES ( 5,517 , 333 ,'Total Size','5000000');
INSERT INTO tblReportData VALUES ( 6,517 , 444 ,'Table Name','Offline Users');
INSERT INTO tblReportData VALUES ( 7,517 , 444 ,'# Rows','100');
INSERT INTO tblReportData VALUES ( 8,517 , 444 ,'Size','500');
INSERT INTO tblReportData VALUES ( 9,517 , 555 ,'URI','C:\File1');

询问

SELECT Row_number() over(order by data_point_group_key) 'Data_id',
       report_id,
       MAX(CASE WHEN data_point = 'Table Name' THEN data_point_value END) as 'Table Name',
       MAX(CASE WHEN data_point = '# Rows' THEN data_point_value END), 
       MAX(CASE WHEN data_point = 'Size' THEN data_point_value END) as 'Size',
       MAX(CASE WHEN data_point = 'Mode Name' THEN data_point_value END) as 'Mode Name',
       MAX(CASE WHEN data_point = 'Total Size' THEN data_point_value END)as 'Total Size',
       MAX(CASE WHEN data_point = 'URI' THEN data_point_value END) as 'URI'
FROM tblReportData 
group by report_id,data_point_group_key

[结果]：

| Data_id | report_id |    Table Name | # of Rows |   Size | Mode Name | Total Size |      URI |
|---------|-----------|---------------|-----------|--------|-----------|------------|----------|
|       1 |       517 |    Work_state |         3 |    100 |    (null) |     (null) |   (null) |
|       2 |       517 |        (null) |    (null) | (null) |   New App |     (null) |   (null) |
|       3 |       517 |        (null) |    (null) | (null) |    (null) |    5000000 |   (null) |
|       4 |       517 | Offline Users |       100 |    500 |    (null) |     (null) |   (null) |
|       5 |       517 |        (null) |    (null) | (null) |    (null) |     (null) | C:\File1 |

sqlfiddle

推荐阅读