PostgreSQL 9.3：将两列合而为一 [英] PostgreSQL 9.3: Union two columns in one
本文介绍了PostgreSQL 9.3：将两列合而为一的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想将下表中的给定记录显示为下表中所示的特定格式。
I want to show the given records in the following table into the specific format which is shown below in the table.
创建表：Test_1
Creating 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 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" = 1, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A102 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" = 0, "B102" = 1, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A103 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" = 0, "B102" = 0, "B103" = 1, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A104 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 1, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A105 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 1, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A106 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 1, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A107 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 1, "B108" = 0, "B109" = 0, "B201" = 0
A108 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 1, "B109" = 0, "B201" = 0
A109 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 1, "B201" = 0
A201 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 1
B101 "A101" = 1, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B102 "A101" = 0, "A102"= 1 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B103 "A101" = 0, "A102"= 0 , "A103"= 1, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B104 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 1, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B105 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 1, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B106 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 1, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B107 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 1 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B108 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 1, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B109 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 1, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B201 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 1, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
以下脚本可以完成一半工作：该脚本仅在第一列和 ColumnB中显示 ColumnA code>在第二列中。
Here is the following script does the half work: This script only display the ColumnA in a first Column and ColumnB in the second column.
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;
推荐答案
经过优化的
WITH t(a, b, ct) AS (
SELECT columna, columnb, count(*)::int
FROM test_1
GROUP BY 1, 2
)
, matrix AS (
SELECT a.*, b.*, COALESCE(t.ct, 0) AS ct
FROM (SELECT DISTINCT ON (a) a, right(a, -1)::int AS sorta FROM t) a
CROSS JOIN (SELECT DISTINCT ON (b) b, right(b, -1)::int AS sortb FROM t) b
LEFT JOIN t USING (a, b)
)
(
SELECT a AS ab, array_agg('"' || b || '" = ' || ct ORDER BY sortb) AS x
FROM matrix
GROUP BY 1, sorta
ORDER BY sorta
)
UNION ALL
(
SELECT b , array_agg('"' || a || '" = ' || ct ORDER BY sorta)
FROM matrix
GROUP BY 1, sortb
ORDER BY sortb
);
需要所有括号。
- Sum results of a few queries and then find top 5 in SQL
这篇关于PostgreSQL 9.3：将两列合而为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
