两列上的 PIVOT 运算符 [英] PIVOT Operator on two columns
问题描述
我对 SQL 非常陌生，更不用说在 SSMS 中进行 PIVOTS 了.在做了一些研究之后，我很难为两列做数据透视.
I'm very new to SQL let alone doing PIVOTS in SSMS. After doing some research, I'm having a hard time doing pivots for two columns.
查询时的数据本身是:
SELECT
INSTRUMENT_ID,
VALUE_TYPE_CODE,
ORIGINAL_TRANSACTION_VALUE,
VALUE_IN_HOME_CURRENCY
FROM
FACT_TABLE
INSTRUMENT_ID | VALUE_TYPE_CODE | ORIGINAL_TRANSACTION_VALUE | VALUE_IN_HOME_CURRENCY
ABC |TOT_AMT | 3 | 5.7702
ABC |AMT_AVAL | 1 | 1.9234
ABC |DRWN_AMT | 2 | 3.8468
ABC |MTD_DRWN | 2 | 3.8468
以ORIGINAL_TRANSACTION_VALUE
SELECT *
FROM
(
SELECT INSTRUMENT_ID,
VALUE_TYPE_CODE,
ORIGINAL_TRANSACTION_VALUE,
VALUE_IN_HOME_CURRENCY
FROM FACT_TABLE
)
AS Table
PIVOT (MAX (ORIGINAL_TRANSACTION_VALUE) FOR VALUE_TYPE_CODE IN ([TOT_AMT],
[AMT_AVAL],
[DRWN_AMT],
[MTD_DRWN])) as piv
结果如预期:
INSTRUMENT_ID | TOT_AMT | AMT_AVAL | DRWN_AMT | MTD_DRWN
ABC |3 | 1 | 2 | 2
但是，我正在尝试生成以下内容 - 请注意每个 VALUE_TYPE_CODE 类别的交替 ORIGINAL_TRANSACTION_VALUE 和 VALUE_IN_HOME_CURRENCY 值
However, I'm trying to produce the following - note the alternating ORIGINAL_TRANSACTION_VALUE and VALUE_IN_HOME_CURRENCY values per category of VALUE_TYPE_CODE
INSTRUMENT_ID |TOT_AMT |TOT_AMT |AMT_AVAL |AMT_AVAL |DRWN_AMT |DRWN_AMT |MTD_DRWN |MTD_DRWN
ABC | 3 |5.7702 | 1 |1.9234 |2 |3.8468 | 2 |3.8468
所以我尝试了从这里找到的以下内容.它看起来很简单，我仍然是新的 SQL，所以对我来说很有意义.复制类别值，否则 PIVOT 将无法正常工作.
So I tried the following that I found from here. It looked simple and I'm still new SQL so it made sense to me. Duplicate the category values or else PIVOT won't work properly.
SELECT *
FROM
(
SELECT INSTRUMENT_ID,
VALUE_TYPE_CODE,
VALUE_TYPE_CODE+'1' as VALUE_TYPE_CODE1,
ORIGINAL_TRANSACTION_VALUE,
VALUE_IN_HOME_CURRENCY
FROM FACT_TABLE
)
AS Table
-- Pivot on ORIGINAL_TRANSACTION_VALUE
PIVOT (MAX (ORIGINAL_TRANSACTION_VALUE) FOR VALUE_TYPE_CODE IN ([TOT_AMT],
[AMT_AVAL],
[DRWN_AMT],
[MTD_DRWN])) as piv
-- Pivot on VALUE_IN_HOME_CURRENCY
PIVOT (MAX (VALUE_IN_HOME_CURRENCY) FOR VALUE_TYPE_CODE1 IN ([TOT_AMT1],
[AMT_AVAL1],
[DRWN_AMT1],
[MTD_DRWN1])) as piv1
不幸的是，这并没有像我希望的那样工作，结果如下:
Unfortunately, this didn't work the way I had hoped for and turned out the following way:
INSTRUMENT_ID |TOT_AMT |TOT_AMT |AMT_AVAL |AMT_AVAL |DRWN_AMT |DRWN_AMT |MTD_DRWN |MTD_DRWN
ABC | 3 |5.7702 | NULL |NULL |NULL |NULL |NULL |NULL
ABC | NULL |NULL | 1 | 1.9234 |NULL |NULL |NULL |NULL
ABC | NULL |NULL | NULL |NULL |2 |3.8468 |NULL |NULL
ABC | NULL |NULL | NULL |NULL |NULL |NULL | 2 |3.8468
我想知道是否有人可以指出我做错了什么?有没有稍微简单一点的方法?本身可能不是一种更简单的方法，而是一种通过一些解释分解的解决方案.我已经在这里查看了一些解决方案，但由于我对此还不熟悉，因此其中很多都让我无法理解.
I'm wondering if anyone can point out what I did wrong? Is there a slightly easier way? Maybe not an easier way per se, but a solution that is broken down with some explanations. I've looked some solutions here, and a lot of them just go over my head since I'm still new to this.
我非常感谢您对此的任何帮助.谢谢.
I very much appreciate any help with this. Thank you.
推荐答案
此答案适用于想要使其动态化的观众.[列值是动态的]
对于 MSSQL SERVER，
For MSSQL SERVER,
DECLARE @cols1 AS NVARCHAR(MAX),@cols2 AS NVARCHAR(MAX),@selectcolumn1 AS NVARCHAR(MAX),@selectcolumn2 AS NVARCHAR(MAX),
@selectcolumn AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(f.VALUE_TYPE_CODE)
FROM FACT_TABLE f
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @cols2 = STUFF((SELECT distinct ',' + QUOTENAME(f.VALUE_TYPE_CODE+'1')
FROM FACT_TABLE f
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @selectcolumn1 = STUFF((SELECT distinct ',MAX(piv1.' + f.VALUE_TYPE_CODE+') as '+ f.VALUE_TYPE_CODE+'_OTV'
FROM FACT_TABLE f
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @selectcolumn2 = STUFF((SELECT distinct ',MAX(piv1.' + f.VALUE_TYPE_CODE+'1) as '+ f.VALUE_TYPE_CODE+'_HC'
FROM FACT_TABLE f
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @selectcolumn=concat(@selectcolumn1,',',@selectcolumn2)
set @query = 'SELECT piv1.INSTRUMENT_ID,
'+@selectcolumn+'
FROM
(
SELECT INSTRUMENT_ID,
VALUE_TYPE_CODE,
VALUE_TYPE_CODE+''1'' as VALUE_TYPE_CODE1,
ORIGINAL_TRANSACTION_VALUE,
VALUE_IN_HOME_CURRENCY
FROM FACT_TABLE
)
AS T
PIVOT (MAX (ORIGINAL_TRANSACTION_VALUE) FOR VALUE_TYPE_CODE IN ('+@cols1+')) as piv
PIVOT (MAX (VALUE_IN_HOME_CURRENCY) FOR VALUE_TYPE_CODE1 IN ('+@cols2+')) as piv1
group by piv1.INSTRUMENT_ID'
execute(@query)
对于 MYSQL 服务器
FOR MYSQL SERVER
SET @col1 = NULL;
SET @col2 = NULL;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'Max(IF(f.VALUE_TYPE_CODE = ''',VALUE_TYPE_CODE,''', ORIGINAL_TRANSACTION_VALUE, Null)) as ',VALUE_TYPE_CODE,'_OT'
)
) INTO @col1
FROM FACT_TABLE;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'Max(IF(f.VALUE_TYPE_CODE1 = ''',VALUE_TYPE_CODE,'1'', VALUE_IN_HOME_CURRENCY, Null)) as ',VALUE_TYPE_CODE,'_HC'
)
) INTO @col2
FROM FACT_TABLE;
SET @sql = CONCAT('SELECT
f.INSTRUMENT_ID, ', CONCAT(@col1,',',@col2) ,
' FROM (SELECT INSTRUMENT_ID,VALUE_TYPE_CODE,CONCAT(VALUE_TYPE_CODE,''1'') AS VALUE_TYPE_CODE1,ORIGINAL_TRANSACTION_VALUE,VALUE_IN_HOME_CURRENCY FROM FACT_TABLE) f
Group by f.INSTRUMENT_ID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这篇关于两列上的 PIVOT 运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
