Sql-server – TSQL help the feeble mind grasp Pivot/Unpivot on two columns

Prior to writing a dynamic SQL query, you should always write a version that is hard-coded so you can get the syntax correct. So the first thing you need to do is write a working PIVOT query for any of the TruckId values that you need.

Static Version:

Let's say you need TruckID = 3, your code for the PIVOT would be similar to the following:

select Name, TruckId, [1], [2], [3], [4], [5], [6]
from
(
  select Name, TruckId, Compartment, Capacity
  from Trucks t
  inner join Compartments c
    on t.Id = c.TruckId
  where t.Id = 3  -- your truck id here
) d
pivot
(
  max(capacity)
  for compartment in ([1], [2], [3], [4], [5], [6])  -- your Compartment values here
) p;

See SQL Fiddle with Demo

Dynamic Version:

Now, your problem is that you are going to have a variety of Compartment values for any of your TruckIds so you need to write something that will accept any TruckId and produce the results that you want. The PIVOT function will work but first you'll need to concatenate a SQL string with all the Compartment values for each TruckId.

First, you'll define all your parameters:

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @ParmDefinition NVARCHAR(500),
  @TruckId as int;

set @TruckId = 3;  -- this would be the value you submit via your SP
set @ParmDefinition = '@id int';

Next, you'll concatenate the list of the new column headers, using FOR XML PATH and STUFF:

select @cols = STUFF((SELECT ',' + QUOTENAME(Compartment) 
                    from #Compartments
                    where TruckId = @TruckId
                    group by id, Compartment
                    order by Id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

Once you have the list of columns, you'll create the full sql string that will be executed. This should be similar to the static version above:

set @query 
    = N'SELECT Name, TruckId, ' + @cols + N' 
        from 
        (
            select t.Name, c.TruckId, c.Compartment, c.Capacity
            from #Trucks t
            inner join #Compartments c
              on t.Id = c.TruckId
            where t.Id = @id
        ) x
        pivot 
        (
            max(Capacity)
            for Compartment in (' + @cols + N')
        ) p ';

The last step is executing the sql string:

exec sp_executesql @query, @ParmDefinition, @id = @TruckId;

See a demo. This gives a result of:

Name    TruckId 1   2   3   4   5   6   7   8   9   10  
------- ------- --- --- --- --- --- --- --- --- --- --- 
BBB-WWW 3       500 500 500 500 500 500 500 500 500 500 

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:

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: