在 SQL Server 中顯示連接 3 個表的屬性值的單個記錄？

我有 3 張桌子

表1（一個視圖）包含記錄 ID、人員姓名、屬性 ID 和輸入到數據庫中與人員有關的所有屬性的新記錄。

表2是表 1 中屬性類型 ID 的描述性標識符名稱

表 3（一個視圖）包含表 1 中記錄 ID 的數據輸入時間戳，用作選擇標準範圍

表1–SQL Server 2014 查看–

ID [char(18)]
Name [nvarchar(50)]
AttributeTypeID [int]
AttributeValue [nvarchar(2000)]

表2 –SQL Server 2014 表–

AttrTypeID [int]
AttrName [nvarchar(50)]

表3 –SQL Server 2014 查看–

EntryID [char(18)]
EntryDateTime [datetime2(3)]

我目前的查詢將它們組合在一起（但是我想要不同的輸出）：

SELECT DISTINCT
Table1.ID
Table1.Name
Table2.AttrTypeID
Table2.AttrName
Table1.AttributeValue

FROM 
Table1 RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID

WHERE
Table1.ID IN (SELECT DISTINCT
            EntryID
            FROM Table3
            WHERE
            EntryDateTime BETWEEN '2018-05-01 00:00:00.000' AND '2018-05-07 00:00:00.000')

ORDER BY
Table1.ID ASC

返回以下記錄：

ID     | Name  | AttrTypeID  | AttrName   | AttributeValue
123456 | Bob   | 201         | Hair Color | Blonde
123456 | Bob   | 202         | Eye Color  | Blue
123456 | Bob   | 203         | Height CM  | 180   
123456 | Bob   | 204         | Weight KG  | 77.1   
123456 | Bob   | 205         | Email      | bob@domain.com   
123456 | Bob   | 206         | Profile    | Text...2000characters...  
789000 | Sarah | 201         | Hair Color | Brown
789000 | Sarah | 202         | Eye Color  | Brown
789000 | Sarah | 203         | Height CM  | 155   
789000 | Sarah | 204         | Weight KG  | 50.5  
789000 | Sarah | 206         | Profile    | Text...2000characters...  

注意 Sarah 在表 1 中沒有電子郵件條目 (AttrTypeID = 205)

我想要的輸出是創建一個返回的查詢：

ID     | Name  | Hair Color  | Eye Color  | Height CM  | Weight KG  | Email           | Profile
123456 | Bob   | Blonde      | Blue       | 180        | 77.1       | bob@domain.com  | Text...2000characters...
789000 | Sarah | Brown       | Brown      | 155        | 50.5       | NULL            | Text...2000characters...

非常感謝您的幫助！

PS我是新來的，所以如果您需要更多資訊或需要不同的格式來提出這個問題，請告訴我。

這是解決此問題的一種非常簡單的方法。

--Setup demo data
Declare @Table1 table
   (ID int, Name varchar(5), AttrTypeID int, AttrName varchar(10), AttributeValue varchar(24))
;

INSERT INTO @Table1
   (ID, Name, AttrTypeID, AttrName, AttributeValue)
VALUES
   (123456, 'Bob', 201, 'Hair Color', 'Blonde'),
   (123456, 'Bob', 202, 'Eye Color', 'Blue'),
   (123456, 'Bob', 203, 'Height CM', '180'),
   (123456, 'Bob', 204, 'Weight KG', '77.1'),
   (123456, 'Bob', 205, 'Email', 'bob@domain.com'),
   (123456, 'Bob', 206, 'Profile', 'Text...2000characters...'),
   (789000, 'Sarah', 201, 'Hair Color', 'Brown'),
   (789000, 'Sarah', 202, 'Eye Color', 'Brown'),
   (789000, 'Sarah', 203, 'Height CM', '155'),
   (789000, 'Sarah', 204, 'Weight KG', '50.5'),
   (789000, 'Sarah', 206, 'Profile', 'Text...2000characters...')
;

--The actual query
SELECT ID, max(name) as Name,
 MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
 MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
 MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
 MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
 MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
 MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM @Table1
GROUP BY id, name;

---

| ID     | Name  | Hair Color | Eye Color | Height CM | Weight KG | Email          | Profile                  |
|--------|-------|------------|-----------|-----------|-----------|----------------|--------------------------|
| 123456 | Bob   | Blonde     | Blue      | 180       | 77.1      | bob@domain.com | Text...2000characters... |
| 789000 | Sarah | Brown      | Brown     | 155       | 50.5      | NULL           | Text...2000characters... |

---

您只需將原始查詢包裝在一個公用表表達式中並使用它來代替 my @Table1. 就像是：

;With Table1 as
(
SELECT DISTINCT Table1.ID Table1.NAME Table2.AttrTypeID Table2.AttrName Table1.AttributeValue
FROM Table1
RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID
WHERE Table1.ID IN (
       SELECT DISTINCT EntryID
       FROM Table3
       WHERE EntryDateTime BETWEEN '2018-05-01 00:00:00.000'
               AND '2018-05-07 00:00:00.000'
       )
) 

--The actual query
SELECT ID, max(name) as Name,
 MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
 MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
 MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
 MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
 MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
 MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM Table1
GROUP BY id, name;

引用自：https://dba.stackexchange.com/questions/206173