如何将枢轴应用于查询结果 [英] How to apply pivot to result of query
问题描述
我当前的查询是
SELECT Name, Code, Today
, Account || Currency as Accounts
FROM (
SELECT
b.description AS Name
, b.contragentidentifycode AS Code
, c.systemday AS Today
, b.accountno AS Account
, b.currencysname AS Currency
FROM vAACCOUNT b, currentdaysetting c
WHERE b.contragentid = 412
AND b.accountno LIKE '26%'
)
它给了我这样的结果:
Name | Code | Today | Accounts
---------------------------------------
name1 | code1 | 07.09.2016 | acc1+curr1
name1 | code1 | 07.09.2016 | acc2+curr1
name1 | code1 | 07.09.2016 | acc1+curr2
name1 | code1 | 07.09.2016 | acc2+curr2
name1 | code1 | 07.09.2016 | acc1+curr3
name1 | code1 | 07.09.2016 | acc2+curr3
name1 | code1 | 07.09.2016 | acc1+curr4
name1 | code1 | 07.09.2016 | acc2+curr4
我需要将此视图转换为:
I need convert this view to:
Name | Code | Today | someName1 | someName2 | someName3 | someName4 | someName5 | someName6 | someName7 | someName8
-------------------------------------------------------------------------------------------------------------------------------------------
name1 | code1 | 07.09.2016 | acc1+curr1 | acc2+curr1 | acc1+curr2 | acc2+curr2 | acc1+curr3 | acc2+curr3 | acc1+curr4 | acc2+curr4
我猜想最有可能为此我必须使用关键字"Pivot".但是我所有的尝试都失败了.我无法将示例中看到的内容投影到我的桌子上.请帮忙.
I guess that most probably for this I have to use the keyword "Pivot". But all my attempts to do so - have failed. I can not to project what I see in the examples, to my table. Please help.
对于列数，我可以添加这样的"id"列:
For number of columns I can add such "id" column:
SELECT id, Name, Code, Today
, Account || Currency as Accounts
FROM (
SELECT
row_number() over (ORDER BY b.id) AS id
, b.description AS Name
...
在我的情况下:
- 帐户数可能不同；
- 名称，代码和数据-每个查询一个；
- accaunt + currency的组合是唯一的；
- 结果应该在一行中；
- 查询结果中的总行数不能超过10(在我的示例中为8)
推荐答案
根据我上面的评论，我认为PIVOT对您不起作用. @RoundFour的答案有效，但要求您知道Account ||的所有可能值并为其编码.货币.这表明这些物品永远不会有新的价值-我发现这不太可能.
Per my comment above, I don't think PIVOT works for you. The answer from @RoundFour works, but requires that you know, and code for, all possible values for Account || Currency. This suggests there will never be new values for these items - I find that unlikely.
以下内容将允许您切换数据的形状.它没有对数据中的值进行任何假设，但确实假设了可能的组合数量限制-我已经编码了八种.
The following will allow you to switch the shape of your data. It makes no assumptions about the values in your data, but it does assume a limit on the number of possible combinations - I have coded for eight.
WITH account_data (name,code,today,account)
AS
(
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr1' FROM dual UNION ALL
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr1' FROM dual UNION ALL
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr2' FROM dual UNION ALL
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr2' FROM dual UNION ALL
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr3' FROM dual UNION ALL
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr3' FROM dual UNION ALL
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr4' FROM dual UNION ALL
SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr4' FROM dual UNION ALL
SELECT 'name2','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr1' FROM dual UNION ALL
SELECT 'name2','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr1' FROM dual UNION ALL
SELECT 'name2','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr2' FROM dual UNION ALL
SELECT 'name3','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr2' FROM dual
)
SELECT
name
,code
,today
,MAX(account1)
,MAX(account2)
,MAX(account3)
,MAX(account4)
,MAX(account5)
,MAX(account6)
,MAX(account7)
,MAX(account8)
FROM
(SELECT
name
,code
,today
,CASE
WHEN rn = 1 THEN account
END account1
,CASE
WHEN rn = 2 THEN account
END account2
,CASE
WHEN rn = 3 THEN account
END account3
,CASE
WHEN rn = 4 THEN account
END account4
,CASE
WHEN rn = 5 THEN account
END account5
,CASE
WHEN rn = 6 THEN account
END account6
,CASE
WHEN rn = 7 THEN account
END account7
,CASE
WHEN rn = 8 THEN account
END account8
FROM
(SELECT
name
,code
,today
,account
,ROW_NUMBER() OVER (PARTITION BY name ORDER BY account) rn
FROM
account_data
)
)
GROUP BY
name
,code
,today
;
更新>>>>>>>>>
UPDATE >>>>>>>>>
上面的WITH ...子句只是因为我的系统中没有表和数据.我已使用您的查询作为指导来重写我的答案-请注意，我无法对此进行测试...
The WITH... clause above is just because I don't have your tables and data in my system. I've rewritten my answer using your query as a guide - please note I have not been able to test this ...
SELECT
name
,code
,today
,MAX(account1)
,MAX(account2)
,MAX(account3)
,MAX(account4)
,MAX(account5)
,MAX(account6)
,MAX(account7)
,MAX(account8)
FROM
(SELECT
name
,code
,today
,CASE
WHEN rn = 1 THEN account
END account1
,CASE
WHEN rn = 2 THEN account
END account2
,CASE
WHEN rn = 3 THEN account
END account3
,CASE
WHEN rn = 4 THEN account
END account4
,CASE
WHEN rn = 5 THEN account
END account5
,CASE
WHEN rn = 6 THEN account
END account6
,CASE
WHEN rn = 7 THEN account
END account7
,CASE
WHEN rn = 8 THEN account
END account8
FROM
(SELECT
b.description AS Name
,b.contragentidentifycode AS Code
,c.systemday AS Today
,b.accountno AS Account
,b.currencysname AS Currency
,b.accountno || b.currencysname AS Accounts
,ROW_NUMBER() OVER (PARTITION BY b.description ORDER BY b.accountno) rn
FROM vAACCOUNT b, currentdaysetting c
WHERE b.contragentid = 412
AND b.accountno LIKE '26%'
)
)
GROUP BY
name
,code
,today
;
这篇关于如何将枢轴应用于查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
