具有动态标头的SQL PIVOT [英] SQL PIVOT with dynamic header
问题描述
我有一个来自女巫的具有以下结构的SQL数据库表，我想为我的用户提供以垂直或水平方式导出数据的选项.
I have a SQL database table with the following structure from witch I'd like to give my users the option to export data in either a vertically or horizontally fashion.
SELECT [MEA_ID], [MEA_DateTime], [PTR_ID], [EXP_ID], [DUN_ID], [MEA_Value]
FROM [MeasurementData];
|--------|----------------------|--------|--------|--------|-----------|
| MEA_ID | MEA_DateTime | PTR_ID | EXP_ID | DUN_ID | MEA_Value |
|--------|----------------------|--------|--------|--------|-----------|
| 1 | 2009-08-10 00:00:00 | 24 | 14 | 2 | 15.1 |
| 2 | 2009-08-10 00:00:00 | 24 | 14 | 3 | 14.3 |
| 3 | 2009-08-10 00:00:00 | 24 | 14 | 4 | 16.7 |
| 4 | 2009-08-10 00:00:10 | 24 | 15 | 2 | 13.0 |
| 5 | 2009-08-10 00:00:10 | 24 | 15 | 4 | 13.4 |
| 6 | 2009-08-10 00:00:20 | 24 | 16 | 2 | 17.8 |
| 7 | 2009-08-10 00:00:20 | 24 | 16 | 3 | 17.7 |
| 8 | 2009-08-10 00:00:20 | 24 | 16 | 4 | 16.2 |
| 9 | 2009-08-10 00:00:00 | 25 | 14 | 3 | 34.0 |
| 10 | 2009-08-10 00:00:00 | 25 | 14 | 4 | 19.0 |
| 11 | 2009-08-10 00:00:10 | 25 | 15 | 2 | 22.1 |
| 12 | 2009-08-10 00:00:10 | 25 | 15 | 3 | 23.1 |
| 13 | 2009-08-10 00:00:20 | 25 | 16 | 2 | 24.6 |
| 14 | 2009-08-10 00:00:20 | 25 | 16 | 3 | 18.3 |
| 15 | 2009-08-10 00:00:20 | 25 | 16 | 4 | 18.2 |
此表为垂直导出.
MEA_DateTime ， PTR_ID ， EXP_ID 和 DUN_ID 的每种组合都是唯一的，因此总只能存在具有给定组合的1行.我要完成的工作是水平旋转 DUN_ID ，以便更好地比较值.
Every combination of MEA_DateTime, PTR_ID, EXP_ID and DUN_ID is unique, so there can always only be 1 row with a given combination. What I am trying to accomplish is to turn the DUN_ID horizontally, to better be able to compare values.
它应该看起来像这样:
SELECT [MEA_DateTime], [PTR_ID], [EXP_ID], [MEA_Value]
FROM [MeasurementData]
PIVOT
(
SUM([MEA_Value])
FOR [DUN_ID] IN (????)
);
DUN_ID DUN_ID DUN_ID
| | |
v v v
|----------------------|--------|--------|-------|-------|-------|
| MEA_DateTime | PTR_ID | EXP_ID | 2 | 3 | 4 |
|----------------------|--------|--------|-------|-------|-------|
| 2009-08-10 00:00:00 | 24 | 14 | 15.1 | 14.3 | 16.7 |
| 2009-08-10 00:00:10 | 24 | 15 | 13.0 | NULL | 13.4 |
| 2009-08-10 00:00:20 | 24 | 16 | 17.8 | 17.7 | 16.2 |
| 2009-08-10 00:00:00 | 25 | 14 | NULL | 34.0 | 19.0 |
| 2009-08-10 00:00:10 | 25 | 15 | 22.1 | 23.1 | NULL |
| 2009-08-10 00:00:20 | 25 | 16 | 24.6 | 18.3 | 18.2 |
我试图使其与PIVOT一起使用，但不幸的是，以前从未做过类似的事情，而且没有太多可展示之处.据我了解，您需要事先知道列标题的名称才能使它起作用，而且我无法弄清楚如何将字段值用作列标题.我想做的是可能的吗?还是以后应该在python中手动构建该结构?
I tried to make it work with a PIVOT, but unfortunately never did something like that before and don't have much to show. From what I could figure out you'd need to know the column header names beforehand for it to work and I couldn't figure out how to use field values as column headers. Is what I'm trying to do possible or should I just build that structure manually in python afterwards?
很高兴获得任何帮助.
编辑:数据库引擎是Microsoft SQL.
The database engine is Microsoft SQL.
解决方案: http://sqlfiddle.com/#!6/3afd7/1/0
推荐答案
您可以使用此查询.
DECLARE @ColNames NVARCHAR(MAX) = ''
SELECT @ColNames = @ColNames + ', ' + QUOTENAME( DUN_ID ) FROM MyTable
GROUP BY DUN_ID
DECLARE @SqlText NVARCHAR(MAX) = '
SELECT * FROM (SELECT MEA_DateTime, PTR_ID, EXP_ID, DUN_ID, MEA_Value FROM MyTable ) SRC
PIVOT( MAX(MEA_Value) FOR DUN_ID IN ( '+ STUFF(@ColNames,1,1,'') +') ) AS PVT ORDER BY PTR_ID, EXP_ID'
EXEC(@SqlText)
结果:
MEA_DateTime PTR_ID EXP_ID 2 3 4
----------------------- ----------- ----------- ---------- -------- --------
2009-08-10 00:00:00.000 24 14 15.10 14.30 16.70
2009-08-10 00:00:10.000 24 15 13.00 NULL 13.40
2009-08-10 00:00:20.000 24 16 17.80 17.70 16.20
2009-08-10 00:00:00.000 25 14 NULL 34.00 19.00
2009-08-10 00:00:10.000 25 15 22.10 23.10 NULL
2009-08-10 00:00:20.000 25 16 24.60 18.30 18.20
这篇关于具有动态标头的SQL PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
