Sql-Server
將屬性轉換為列
我有一個類型列表：
SELECT * FROM type;id name ----------- -------------------------------------------------- 1 person 2 other god 3 location 4 role 5 gender以及一個對象列表，每個對像都有一個類型：
SELECT * FROM object; id name type_id ----------- -------------------------------------------------- ----------- 1 Adam 1 2 Eve 1 3 Cain 1 4 Abel 1 5 Jeroboam 1 6 Zeredah 3以及顯示類型名稱的視圖：
SELECT * FROM object_view; id name type_name ----------- -------------------------------------------------- -------------------------------------------------- 1 Adam person 2 Eve person 3 Cain person 4 Abel person 5 Jeroboam person 6 Zeredah location關係類型列表：
SELECT * FROM relationship; id name ----------- -------------------------------------------------- 1 has father 2 has mother 3 from以及對象之間的關係列表：
SELECT * FROM object_relationship; object_a_id relationship_id object_b_id ----------- --------------- ----------- 4 1 1 3 2 2 5 3 6以及對這些關係的看法：
SELECT * FROM object_relationship_view; object_a relationship object_b -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- Abel has father Adam Cain has mother Eve Jeroboam from Zeredah我想用 、 和 列列出
father每個mother對象from。如果對像不具有這些屬性之一，則該列應顯示NULL. 所以結果應該是這樣的：
這是一種似乎可行的方法：
SELECT id, object.name, ( SELECT (SELECT name FROM object WHERE id = object_b_id) FROM object_relationship WHERE object.id = object_relationship.object_a_id AND object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has father') ) AS father, ( SELECT (SELECT name FROM object WHERE id = object_b_id) FROM object_relationship WHERE object.id = object_relationship.object_a_id AND object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother') ) AS mother, ( SELECT (SELECT name FROM object WHERE id = object_b_id) FROM object_relationship WHERE object.id = object_relationship.object_a_id AND object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'from') ) AS [from] FROM object;我的問題是：這可以通過 JOIN 來完成嗎？
這種方法很接近：
SELECT object.name, (SELECT name FROM object WHERE id = REL_FATHER.object_b_id) AS father, (SELECT name FROM object WHERE id = REL_MOTHER.object_b_id) AS mother, (SELECT name FROM object WHERE id = REL_FROM.object_b_id) AS [from] FROM object LEFT JOIN object_relationship AS REL_FATHER ON object.id = REL_FATHER.object_a_id LEFT JOIN object_relationship AS REL_MOTHER ON object.id = REL_MOTHER.object_a_id LEFT JOIN object_relationship AS REL_FROM ON object.id = REL_FROM.object_a_id WHERE REL_FATHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has father') AND REL_MOTHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother') AND REL_FROM.relationship_id = (SELECT id FROM relationship WHERE name = 'from');這種方法的問題在於它只列出了具有和值
father的對象。如果其中任何一個為NULL，則不會列出它們。mother``from`因此，例如，如果您有以下添加的關係數據：
EXEC insert_object_relationship 'Abel', 'has father', 'Adam'; EXEC insert_object_relationship 'Abel', 'has mother', 'Eve'; EXEC insert_object_relationship 'Abel', 'from', 'Eden'; EXEC insert_object_relationship 'Cain', 'has father', 'Adam'; EXEC insert_object_relationship 'Cain', 'has mother', 'Eve'; EXEC insert_object_relationship 'Cain', 'from', 'Eden'; EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';上面的查詢返回以下內容：
（請注意未列出，因為該條目與和
Zeredah沒有關係。father``mother有沒有比上面顯示的方法更好的方法？
我確信上面描述的技術不是新的；歡迎任何指向討論此問題的參考文獻的指針。（即在數據庫理論文本中有這個名稱嗎？）
生成這些表和數據所需的所有程式碼都包含在下面。
如果你覺得這個問題更適合stackoverflow，請告訴我，我會在那裡問。
感謝您的任何建議！
DROP TABLE IF EXISTS object_relationship; DROP TABLE IF EXISTS object; -------------------------------------------------------------------------------- DROP TABLE IF EXISTS type; CREATE TABLE type ( id INT NOT NULL PRIMARY KEY IDENTITY(1, 1), name nvarchar(50) NOT NULL ); -------------------------------------------------------------------------------- CREATE TABLE object ( id INT NOT NULL PRIMARY KEY IDENTITY(1, 1), name nvarchar(50) NOT NULL, type_id int NOT NULL CONSTRAINT FK_object_type FOREIGN KEY REFERENCES type(id) ); -------------------------------------------------------------------------------- DROP TABLE IF EXISTS relationship; CREATE TABLE relationship ( id INT NOT NULL PRIMARY KEY IDENTITY(1, 1), name nvarchar(50) NOT NULL ); -------------------------------------------------------------------------------- CREATE TABLE object_relationship ( object_a_id INT CONSTRAINT FK_object_relationship_object_object_a FOREIGN KEY REFERENCES object(id), relationship_id INT CONSTRAINT FK_object_relationship_relationship FOREIGN KEY REFERENCES relationship(id), object_b_id INT CONSTRAINT FK_object_relationship_object_object_b FOREIGN KEY REFERENCES object(id) ); -------------------------------------------------------------------------------- DROP VIEW IF EXISTS object_view; GO CREATE VIEW object_view AS SELECT object.id, object.name AS name, type.name AS type_name FROM object INNER JOIN type ON object.type_id = type.id; GO -------------------------------------------------------------------------------- DROP VIEW IF EXISTS object_relationship_view; GO CREATE VIEW object_relationship_view AS SELECT A.name AS object_a, relationship.name AS relationship, B.name AS object_b FROM object AS A INNER JOIN object_relationship ON A.id = object_relationship.object_a_id INNER JOIN relationship ON object_relationship.relationship_id = relationship.id INNER JOIN object AS B ON B.id = object_relationship.object_b_id; GO -------------------------------------------------------------------------------- INSERT INTO type (name) VALUES ('person'), ('other god'), ('location'), ('role'), ('gender'); DROP PROC IF EXISTS insert_object; GO CREATE PROC insert_object @object AS nvarchar(50), @type AS nvarchar(50) AS INSERT INTO object (name, type_id) VALUES (@object, (SELECT id FROM type WHERE name = @type)); GO -------------------------------------------------------------------------------- EXEC insert_object 'Adam', 'person'; EXEC insert_object 'Eve', 'person'; EXEC insert_object 'Cain', 'person'; EXEC insert_object 'Abel', 'person'; EXEC insert_object 'Jeroboam', 'person'; EXEC insert_object 'Zeredah', 'location'; EXEC insert_object 'Eden', 'location'; -------------------------------------------------------------------------------- INSERT INTO relationship (name) VALUES ('has father'), ('has mother'), ('from'); -------------------------------------------------------------------------------- DROP PROC IF EXISTS insert_object_relationship; GO CREATE PROC insert_object_relationship @a AS nvarchar(50), @relationship AS nvarchar(50), @b AS nvarchar(50) AS INSERT INTO object_relationship (object_a_id, relationship_id, object_b_id) VALUES ((SELECT id FROM object WHERE name = @a), (SELECT id FROM relationship WHERE name = @relationship), (SELECT id FROM object WHERE name = @b)); GO -------------------------------------------------------------------------------- EXEC insert_object_relationship 'Abel', 'has father', 'Adam'; EXEC insert_object_relationship 'Cain', 'has mother', 'Eve'; EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';
我確信上面描述的技術不是新的……
對，我認為您正在尋找的術語是“樞軸”。您可以使用 T-SQL
PIVOT運算符來執行此操作：SELECT pivot_table.aName, pivot_table.[has mother], pivot_table.[has father], pivot_table.[from] FROM ( SELECT oA.[name] AS aName, oB.[name] AS bName, r.[name] AS rName FROM dbo.[object] oA LEFT JOIN dbo.object_relationship ore ON ore.object_a_id = oA.id LEFT JOIN dbo.relationship r ON r.id = ore.relationship_id LEFT JOIN dbo.[object] oB ON ob.id = ore.object_b_id ) source_table PIVOT ( MAX(bName) FOR rName IN ([has mother], [has father], [from]) ) AS pivot_table ORDER BY pivot_table.aName;以及文章底部腳本中給出的範例數據的結果：
