未透视行上具有特定于该行的硬编码值的输出列 [英] Output column on unpivoted row(s) with hardcoded value specific to that row
本文介绍了未透视行上具有特定于该行的硬编码值的输出列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下查询，它使用unpivot将FREIGHT_AMT和/或SALETX_AMT中的值输出到MERCHANDISE_AMT列，以便我可以将它们作为单独的发票行输出。
对于我输出的任何未透视的行(FREIGHT_AMT，SALETAX_AMT)，我需要做的是输出FREIGHT_AMT行中的字符串‘Freight’(对于FREIGHT_AMT行)和SALETX_AMT中的‘Tax’行(对于Line_Type)。
通常我会考虑在原始查询的CASE语句中执行此操作，但是，因为在这一点上，这些仍然是列而不是行，我不确定如何才能正确地指示这一点。
WITH CTE AS (
SELECT CONCAT(A.BUSINESS_UNIT,A.VOUCHER_ID) AS INVOICE_ID, A.VOUCHER_LINE_NUM,
CASE WHEN EXISTS (SELECT 1 FROM PS_DISTRIB_LINE
WHERE BUSINESS_UNIT = A.BUSINESS_UNIT
AND VOUCHER_ID = A.VOUCHER_ID
AND VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM)
THEN 'ITEM' ELSE 'MISCELLANEOUS' END AS LineType, A.MERCHANDISE_AMT, C.FREIGHT_AMT
, C.SALETX_AMT
FROM PS_VOUCHER_LINE A
LEFT OUTER JOIN PS_DISTRIB_LINE B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.VOUCHER_ID = A.VOUCHER_ID AND B.VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM
LEFT OUTER JOIN PS_VOUCHER C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.VOUCHER_ID = A.VOUCHER_ID
LEFT OUTER JOIN PS_PYMNT_VCHR_XREF I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT AND I.VOUCHER_ID = A.VOUCHER_ID
WHERE
C.INVOICE_DT > '01-03-2019'
AND C.ACCOUNTING_DT < '06-01-2021'
AND I.PYMNT_ID = ''
AND C.CLOSE_STATUS <> 'C'
AND A.VOUCHER_ID IN ('00720667', '00721196', '00721021', '00721171', '00326705'))
SELECT INVOICE_ID, VOUCHER_LINE_NUM - 1 + row_number() over
(PARTITION BY INVOICE_ID, VOUCHER_LINE_NUM ORDER BY VOUCHER_LINE_NUM) AS VOUCHER_LINE_NUM,LineType ,Value
FROM CTE
unpivot (Value FOR col IN (MERCHANDISE_AMT, FREIGHT_AMT, SALETX_AMT)) u
WHERE Value <> 0.00
未透视查询的所需输出：
INVOICE_ID VOUCHER_LINE_NUM LineType Value
1100000720667 1 ITEM 17480.820
1100000720667 2 FREIGHT 59.500
1100000721196 5 ITEM 4680.200
1100000721196 6 FREIGHT 55.500
1100000721196 7 TAX 512.890
编辑：包括我当前数据的样本数据：
--CREATE TABLES:
CREATE TABLE #TempVOUCHER_LINE (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), VOUCHER_LINE_NUM varchar (4), MERCHANDISE_AMT decimal (7,3))
CREATE TABLE #TempDISTRIB_LINE (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), VOUCHER_LINE_NUM varchar (4))
CREATE TABLE #TempVOUCHER (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), FREIGHT_AMT decimal (7,3), SALETX_AMT decimal (7,3), INVOICE_DT datetime, ACCOUNTING_DT datetime, CLOSE_STATUS varchar(4) )
CREATE TABLE #TempPYMNT_VCHR_XREF (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), PYMNT_ID varchar (10))
--INSERT DATA INTO TEMP TABLES:
INSERT INTO #TempVOUCHER_LINE
(BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM, MERCHANDISE_AMT)
VALUES ('11000', '00720667', '1', 1748.820),
('11000', '00721196', '5', 4680.200)
INSERT INTO #TempDISTRIB_LINE
(BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM)
VALUES ('11000', '00720667', '1'),
('11000', '00721196', '5')
INSERT INTO #TempVOUCHER
(BUSINESS_UNIT, VOUCHER_ID, FREIGHT_AMT, SALETX_AMT, INVOICE_DT, ACCOUNTING_DT, CLOSE_STATUS)
VALUES ('11000', '00720667', 59.50, 0.00, '2019-05-03', '2014-05-14', 'A'),
('11000', '00721196', 55.50, 512.890, '2020-08-10', '2020-08-12', 'A')
INSERT INTO #TempPYMNT_VCHR_XREF
(BUSINESS_UNIT, VOUCHER_ID, PYMNT_ID)
VALUES ('11000', '00720667', ''),
('11000', '00721196', '')
--QUERY FROM TEMP TABLES:
;
WITH CTE AS (
SELECT CONCAT(A.BUSINESS_UNIT,A.VOUCHER_ID) AS INVOICE_ID, A.VOUCHER_LINE_NUM,
CASE WHEN EXISTS (SELECT 1 FROM #TempDISTRIB_LINE
WHERE BUSINESS_UNIT = A.BUSINESS_UNIT
AND VOUCHER_ID = A.VOUCHER_ID
AND VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM)
THEN 'ITEM' ELSE 'MISCELLANEOUS' END AS LineType, A.MERCHANDISE_AMT, C.FREIGHT_AMT
, C.SALETX_AMT
FROM #TempVOUCHER_LINE A
LEFT OUTER JOIN #TempDISTRIB_LINE B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.VOUCHER_ID = A.VOUCHER_ID AND B.VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM
LEFT OUTER JOIN #TempVOUCHER C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.VOUCHER_ID = A.VOUCHER_ID
LEFT OUTER JOIN #TempPYMNT_VCHR_XREF I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT AND I.VOUCHER_ID = A.VOUCHER_ID
WHERE
C.INVOICE_DT > '01-03-2019'
AND C.ACCOUNTING_DT < '06-01-2021'
AND I.PYMNT_ID = ''
AND C.CLOSE_STATUS <> 'C'
AND A.VOUCHER_ID IN ('00720667', '00721196', '00721021', '00721171', '00326705'))
SELECT INVOICE_ID, VOUCHER_LINE_NUM - 1 + row_number() over (PARTITION BY INVOICE_ID, VOUCHER_LINE_NUM ORDER BY VOUCHER_LINE_NUM) AS VOUCHER_LINE_NUM, LineType --CASE WHEN FREIGHT_AMT <> 0.00 THEN 'FREIGHT' WHEN SALETX_AMT <> 0.00 THEN 'TAX' ELSE 'ITEM' END AS LineType
,Value
FROM CTE
unpivot (Value FOR col IN (MERCHANDISE_AMT, FREIGHT_AMT, SALETX_AMT)) u
WHERE Value <> 0.00
当前产量：
INVOICE_ID VOUCHER_LINE_NUM LineType Value
1100000720667 1 ITEM 1748.820
1100000720667 2 ITEM 59.500
1100000721196 5 ITEM 4680.200
1100000721196 6 ITEM 55.500
1100000721196 7 ITEM 512.890
所需输出：
INVOICE_ID VOUCHER_LINE_NUM LineType Value
1100000720667 1 ITEM 1748.820
1100000720667 2 FREIGHT 59.500
1100000721196 5 ITEM 4680.200
1100000721196 6 FREIGHT 55.500
1100000721196 7 TAX 512.890
重复错误(基于拉努的回答：)
编辑2：
CREATE TABLE #TempVOUCHER_LINE (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), VOUCHER_LINE_NUM varchar (4), MERCHANDISE_AMT decimal (8,3))
--DROP TABLE #TempDISTRIB_LINE
CREATE TABLE #TempDISTRIB_LINE (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), VOUCHER_LINE_NUM varchar (4))
--DROP TABLE #TempVOUCHER
CREATE TABLE #TempVOUCHER (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), FREIGHT_AMT decimal (7,3), SALETX_AMT decimal (7,3), INVOICE_DT datetime, ACCOUNTING_DT datetime, CLOSE_STATUS varchar(4) )
--DROP TABLE #TempPYMNT_VCHR_XREF
CREATE TABLE #TempPYMNT_VCHR_XREF (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), PYMNT_ID varchar (10))
--DROP TABLE #TempVOUCHER_LINE
INSERT INTO #TempVOUCHER_LINE
(BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM, MERCHANDISE_AMT)
VALUES ('11000', '00720667', '1', 17480.820),
('11000', '00721196', '5', 4680.200),
('11000', '00721021', '1', 82.64),
('11000', '00721021', '2', 542.12),
('11000', '00721021', '3', 126.89),
('11000', '00721021', '4', 1022.03)
--DROP TABLE #TempDISTRIB_LINE
INSERT INTO #TempDISTRIB_LINE
(BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM)
VALUES ('11000', '00720667', '1'),
('11000', '00721196', '5'),
('11000', '00721021', '1'),
('11000', '00721021', '2'),
('11000', '00721021', '3'),
('11000', '00721021', '4')
--SELECT * FROM #TempVOUCHER
--DROP TABLE #TempVOUCHER
INSERT INTO #TempVOUCHER
(BUSINESS_UNIT, VOUCHER_ID, FREIGHT_AMT, SALETX_AMT, INVOICE_DT, ACCOUNTING_DT, CLOSE_STATUS)
VALUES ('11000', '00720667', 59.50, 0.00, '05-03-2019', '05-14-2014', 'A'),
('11000', '00721196', 55.50, 512.890, '08-10-2020', '08-12-2020', 'A'),
('11000', '00721021', 129.01, 0.00, '12-13-2019', '12-16-2019', 'A')
--DROP TABLE #TempPYMNT_VCHR_XREF
INSERT INTO #TempPYMNT_VCHR_XREF
(BUSINESS_UNIT, VOUCHER_ID, PYMNT_ID)
VALUES ('11000', '00720667', ''),
('11000', '00721196', ''),
('11000', '00721021', '')
;
--SQL QUERY:
WITH CTE AS
(SELECT CONCAT(A.BUSINESS_UNIT, A.VOUCHER_ID) AS INVOICE_ID,
A.VOUCHER_LINE_NUM,
A.MERCHANDISE_AMT,
C.FREIGHT_AMT,
C.SALETX_AMT
FROM #TempVOUCHER_LINE A
LEFT OUTER JOIN #TempDISTRIB_LINE B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT
AND B.VOUCHER_ID = A.VOUCHER_ID
AND B.VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM
LEFT OUTER JOIN #TempVOUCHER C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT
AND C.VOUCHER_ID = A.VOUCHER_ID
LEFT OUTER JOIN #TempPYMNT_VCHR_XREF I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT
AND I.VOUCHER_ID = A.VOUCHER_ID
WHERE C.INVOICE_DT > '01-03-2019'
AND C.ACCOUNTING_DT < '06-01-2021'
AND I.PYMNT_ID = ''
AND C.CLOSE_STATUS <> 'C'
AND A.VOUCHER_ID IN ('00720667', '00721196', '00721021', '00721171', '00326705'))
SELECT C.INVOICE_ID,
C.VOUCHER_LINE_NUM + ROW_NUMBER() OVER (PARTITION BY C.INVOICE_ID, VOUCHER_LINE_NUM ORDER BY VOUCHER_LINE_NUM) - 1 AS VOUCHER_LINE_NUM,
V.LineType,
V.[Value]
FROM CTE C
CROSS APPLY (VALUES('Merchandise',C.MERCHANDISE_AMT),
('Freight',C.FREIGHT_AMT),
('Tax',C.SALETX_AMT))V(LineType, Value)
WHERE V.[Value] <> 0;
推荐答案
我认为这就是您想要的。我不喜欢使用限制性UNPIVOT运算符，而是使用VALUES表结构。注意：您需要替换{Some Expression}Will定义行编号顺序的表达式：
WITH CTE AS
(SELECT CONCAT(A.BUSINESS_UNIT, A.VOUCHER_ID) AS INVOICE_ID,
A.VOUCHER_LINE_NUM,
A.MERCHANDISE_AMT,
C.FREIGHT_AMT,
C.SALETX_AMT
FROM #TempVOUCHER_LINE A
LEFT OUTER JOIN #TempDISTRIB_LINE B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT
AND B.VOUCHER_ID = A.VOUCHER_ID
AND B.VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM
LEFT OUTER JOIN #TempVOUCHER C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT
AND C.VOUCHER_ID = A.VOUCHER_ID
LEFT OUTER JOIN #TempPYMNT_VCHR_XREF I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT
AND I.VOUCHER_ID = A.VOUCHER_ID
WHERE C.INVOICE_DT > '01-03-2019'
AND C.ACCOUNTING_DT < '06-01-2021'
AND I.PYMNT_ID = ''
AND C.CLOSE_STATUS <> 'C'
AND A.VOUCHER_ID IN ('00720667', '00721196', '00721021', '00721171', '00326705'))
SELECT C.INVOICE_ID,
C.VOUCHER_LINE_NUM + ROW_NUMBER() OVER (PARTITION BY C.INVOICE_ID ORDER BY {Some Expression}) - 1 AS VOUCHER_LINE_NUM,
V.LineType,
V.[Value]
FROM CTE C
CROSS APPLY (VALUES('Merchandise',C.MERCHANDISE_AMT),
('Freight',C.FREIGHT_AMT),
('Tax',C.SALETX_AMT))V(LineType, Value)
WHERE V.[Value] <> 0;
这篇关于未透视行上具有特定于该行的硬编码值的输出列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
