如何將多個重複列轉換為具有讚賞值的單行

如何將多個重複列轉換為具有讚賞值的單行，請參閱我的實際表格和預期結果的圖像。

這是一個使用PIVOT來實現您想要的結果的範例。

--Demo setup
Declare @Table table (FormId int, FieldName varchar(10), [value] varchar(10)) 
insert into @Table(FormId, FieldName, [value]) values
(123,'FirstName','aaaaa'),
(123,'LastName','bbbb'),
(123,'Email','kk@g.com'),
(124,'FirstName','cccc'),
(124,'LastName','dddd'),
(124,'Email','ll@g.com'),
(125,'FirstName','eeee'),
(125,'LastName','ffff'),
(125,'Email','abc@.com')

--The solution
SELECT FormId
   ,FirstName
   ,LastName
   ,Email
FROM (
   SELECT Formid
       ,FieldName
       ,[Value]
   FROM @Table
   ) AS SourceTable
PIVOT(MIN([Value]) --Needs to be an aggregate function
       FOR [FieldName] IN (
           FirstName
           ,LastName
           ,Email
           )) AS PivotOutput

---

| FormId | FirstName | LastName | Email    |
|--------|-----------|----------|----------|
| 123    | aaaaa     | bbbb     | kk@g.com |
| 124    | cccc      | dddd     | ll@g.com |
| 125    | eeee      | ffff     | abc@.com |

您可以通過在網際網路上搜尋“樞軸鍵值對”來找到更多範例

WITH t0 AS ( SELECT DISTINCT formid
            FROM table ) 
SELECT t0.formid, t1.value FirstName, t2.value LastName, t3.value Email
FROM t0
LEFT JOIN table t1 ON t0.formid = t1.formid AND t1.fieldname = 'FirstName'
LEFT JOIN table t2 ON t0.formid = t2.formid AND t1.fieldname = 'LastName'
LEFT JOIN table t3 ON t0.formid = t3.formid AND t1.fieldname = 'Email'

(formid, fieldname)在源表中必須定義為唯一的。

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