SQL Server Pivot – How to Pivot and Unpivot Data

pivotsql serversql-server-2008-r2t-sqlunpivot

This is the pivot I made:

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

I need that this:

enter image description here

Become this:

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

SQL is giving me error sintax in everything I try.

This is the fiddle of the table:

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

Best Answer

Why do you need to convert the average into a varchar? Also, why do you have two extra SELECT * FROM (...) AS T0 wrapping the main select statement?

This runs without error:

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;

Results:

enter image description here

Perhaps I really don't understand what you are attempting to do, or perhaps this is just a code fragment, or perhaps my sample data is incorrect, however the following query returns the same result as the above 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

This can be further simplified by removing the CROSS JOINs and performing a PIVOT, as per this:

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;

Results:

enter image description here

Related Question