PostgreSQL 9.3:使用array_agg函数以特定格式显示结果 [英] PostgreSQL 9.3: Display result in specific format using array_agg function
本文介绍了PostgreSQL 9.3:使用array_agg函数以特定格式显示结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将下表中的给定记录显示为特定格式 如下表所示.
I want to show the given records in the following table into the specific format which is shown below in the table.
创建表格:Test_1
CREATE TABLE Test_1
(
ColumnA varchar,
ColumnB varchar
);
插入记录:
INSERT INTO Test_1 values('A101','B101'),('A102','B102'),
('A103','B103'),('A104','B104'),
('A105','B105'),('A106','B106'),
('A107','B107'),('A108','B108'),
('A109','B109'),('A201','B201');
我想显示这样的结果:
预期结果:
ColumnA ColumnX
---------------------------------------------------------------------------------------------------------------------------------------
A101 "B101" = 1, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A102 "B101" = 0, "B102" = 1, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A103 "B101" = 0, "B102" = 0, "B103" = 1, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A104 "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 1, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A105 "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 1, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A106 "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 1, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A107 "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 1, "B108" = 0, "B109" = 0, "B201" = 0
A108 "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 1, "B109" = 0, "B201" = 0
A109 "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 1, "B201" = 0
A201 "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 1
我正在使用以下脚本来完成我的工作:
For which I am using the following script which does my half job:
SELECT columnA, array_agg(ColumnB) AS ColumnX
FROM (
SELECT ColumnA, right(ColumnA, -1)::int AS sortb
, CASE WHEN m.ColumnB IS NULL THEN 0 ELSE 1 END AS ColumnB
FROM (SELECT DISTINCT ColumnA FROM Test_1) b
CROSS JOIN (SELECT DISTINCT ColumnB FROM Test_1) a
LEFT JOIN Test_1 m USING (ColumnA, ColumnB)
ORDER BY sortb, right(ColumnB, -1)::int
) sub
GROUP BY 1, sortb
ORDER BY sortb;
上面的脚本给了我以下结果:
The above script gives me the following result:
获得结果:
ColumnA ColumnX
---------------------------------------
A101 {1,0,0,0,0,0,0,0,0,0}
A102 {0,1,0,0,0,0,0,0,0,0}
A103 {0,0,1,0,0,0,0,0,0,0}
A104 {0,0,0,1,0,0,0,0,0,0}
A105 {0,0,0,0,1,0,0,0,0,0}
A106 {0,0,0,0,0,1,0,0,0,0}
A107 {0,0,0,0,0,0,1,0,0,0}
A108 {0,0,0,0,0,0,0,1,0,0}
A109 {0,0,0,0,0,0,0,0,1,0}
A201 {0,0,0,0,0,0,0,0,0,1}
问题:如何在获取ColumnX
的值之前添加columnB
值?
Question: How to add columnB
values infront of values of getting in ColumnX
?
推荐答案
这只是一个简单的修改.
还根据您的评论中的要求添加了实际计数.
It's just a simple modification.
Also added the actual count as requested in your comment.
SELECT columnA, array_agg(ColumnB) AS ColumnX
FROM (
SELECT ColumnA, right(ColumnA, -1)::int AS sorta
, '"' || ColumnB || '" = ' || count(m.ColumnB) AS ColumnB
FROM (SELECT DISTINCT ColumnA FROM Test_1) b
CROSS JOIN (SELECT DISTINCT ColumnB FROM Test_1) a
LEFT JOIN Test_1 m USING (ColumnA, ColumnB)
GROUP BY ColumnA, ColumnB
ORDER BY sorta, right(ColumnB, -1)::int
) sub
GROUP BY 1, sorta
ORDER BY sorta;
根据评论:
SELECT ARRAY[columnA] || array_agg(ColumnB) AS ColumnX
FROM (
SELECT ColumnA, right(ColumnA, -1)::int AS sorta
, '"' || ColumnB || '" = ' || count(m.ColumnB) AS ColumnB
FROM (SELECT DISTINCT ColumnA FROM Test_1) b
CROSS JOIN (SELECT DISTINCT ColumnB FROM Test_1) a
LEFT JOIN Test_1 m USING (ColumnA, ColumnB)
GROUP BY ColumnA, ColumnB
ORDER BY right(ColumnB, -1)::int, sorta
) sub
GROUP BY columnA, sorta
ORDER BY sorta;
这篇关于PostgreSQL 9.3:使用array_agg函数以特定格式显示结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文