如何旋轉這個 unpivot？

這是我做的支點：

SELECT *
FROM (
   SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao
   FROM (
       SELECT *
       FROM (
           SELECT 39 as codigo
               , (ROW_NUMBER() over 
                   (
                   PARTITION by T1.CodNrQuestao 
                   ORDER BY T1.CodNrQuestao
                   )
               ) as ID
               , 0 as Grupo
               , T1.Opcao as CodOpcao_1
               , T2.Opcao as CodOpcao_2
               , T3.Opcao as CodOpcao_3
               , T4.Opcao as CodOpcao_4
           From tblQuestaoOpcao T1
               Cross Join tblQuestaoOpcao T2
               Cross Join tblQuestaoOpcao T3
               Cross Join tblQuestaoOpcao T4
           Where T1.codNrQuestao = 811962
               And T2.CodNRQuestao = 811757
               And T3.CodNRQuestao = 812009
               And T4.CodNRQuestao = 812011
       )  as T0
       UNPIVOT (
       [CodOpcao] FOR [DescQuestao] IN (
       [CodOpcao_1]
       , [CodOpcao_2]
       , [CodOpcao_3]
       , [CodOpcao_4]
       )
       ) AS U
   )  as T0
)  as T0   
PIVOT ( -- from here I start to get errors 
   CONVERT(VARCHAR, AVG(codOpcao)) 
   FOR descQuestao IN (
       CodOpcao_1
       , CodOpcao_2
       , CodOpcao_3
       , CodOpcao_4
       )
) AS U

我需要這個：

變成這樣：

CODIGO | ID | GRUPO | CODOPÇÃO_1 | CODOPÇÃO_2 | CODOPÇÃO_3 | CODOPÇÃO_4
39      1     0        

SQL 在我嘗試的所有事情中都給了我錯誤的 sintax。

這是桌子的小提琴：

http://sqlfiddle.com/#!3/8d3d0

為什麼需要將平均值轉換為 varchar？另外，為什麼你有兩個額外的SELECT * FROM (...) AS T0包裝主選擇語句？

這執行沒有錯誤：

USE tempdb;

CREATE TABLE [dbo].[tblQuestaoOpcao](
   [CodQuestaoOpcao] [int] IDENTITY(1,1) NOT NULL,
   [CodNRQuestao] [int] NULL,
   [Opcao] [int] NULL,
   [DescOpcao] [varchar](5000) NULL,
CONSTRAINT [PK_tblQuestaoOpcao] PRIMARY KEY CLUSTERED 
(
   [CodQuestaoOpcao] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
   , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
   , FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO dbo.tblQuestaoOpcao(CodNRQuestao, Opcao, DescOpcao)
VALUES (811962, 2, 'CodOpcao_1')
   , (811757, 3, 'CodOpcao_2')
   , (812009, 3, 'CodOpcao_3')
   , (812011, 3, 'CodOpcao_4')

SELECT *
FROM (
   SELECT 39 as codigo
       , (ROW_NUMBER() over 
           (
           PARTITION by T1.CodNrQuestao 
           ORDER BY T1.CodNrQuestao
           )
       ) as ID
       , 0 as Grupo
       , T1.Opcao as CodOpcao_1
       , T2.Opcao as CodOpcao_2
       , T3.Opcao as CodOpcao_3
       , T4.Opcao as CodOpcao_4
   From tblQuestaoOpcao T1
       Cross Join tblQuestaoOpcao T2
       Cross Join tblQuestaoOpcao T3
       Cross Join tblQuestaoOpcao T4
   Where T1.codNrQuestao = 811962
       And T2.CodNRQuestao = 811757
       And T3.CodNRQuestao = 812009
       And T4.CodNRQuestao = 812011
)  as T0
UNPIVOT (
[CodOpcao] FOR [DescQuestao] IN (
[CodOpcao_1]
, [CodOpcao_2]
, [CodOpcao_3]
, [CodOpcao_4]
)
) AS U
PIVOT ( -- from here I start to get errors 
   AVG(codOpcao) 
   FOR descQuestao IN (
       CodOpcao_1
       , CodOpcao_2
       , CodOpcao_3
       , CodOpcao_4
       )
) AS U;

結果：

也許我真的不明白您要做什麼，或者這只是一個程式碼片段，或者我的範例數據不正確，但是以下查詢返回與上述相同的結果PIVOT / UNPIVOT：

SELECT 39 as codigo
   , (ROW_NUMBER() over 
       (
       PARTITION by T1.CodNrQuestao 
       ORDER BY T1.CodNrQuestao
       )
   ) as ID
   , 0 as Grupo
   , T1.Opcao as CodOpcao_1
   , T2.Opcao as CodOpcao_2
   , T3.Opcao as CodOpcao_3
   , T4.Opcao as CodOpcao_4
From tblQuestaoOpcao T1
   Cross Join tblQuestaoOpcao T2
   Cross Join tblQuestaoOpcao T3
   Cross Join tblQuestaoOpcao T4
Where T1.codNrQuestao = 811962
   And T2.CodNRQuestao = 811757
   And T3.CodNRQuestao = 812009
   And T4.CodNRQuestao = 812011

這可以通過刪除CROSS JOINs並執行 a來進一步簡化PIVOT，如下所示：

SELECT *
FROM (
SELECT 39 as codigo
   , (ROW_NUMBER() over 
       (
       PARTITION by T1.CodNrQuestao 
       ORDER BY T1.CodNrQuestao
       )
   ) as ID
   , 0 as Grupo
   , DescOpcao
   , Opcao
FROM tblQuestaoOpcao T1
) pvt
PIVOT (
AVG(Opcao) FOR DescOpcao IN 
   (
       [CodOpcao_1]
       , [CodOpcao_2]
       , [CodOpcao_3]
       , [CodOpcao_4]
   )
) u;

結果：

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