Início / dba / Perguntas / 63698
Accepted
jamauss
Asked: 2014-04-23 18:15:09 +0800 CST

Precisa de ajuda com o SQL Server PIVOT

  • 772

Eu sei sobre o recurso PIVOT no SQL Server e li algumas outras perguntas e respostas aqui, mas simplesmente não consigo entendê-lo para fazer o que preciso. Estou tentando fazer alguns cálculos e tenho o seguinte:

TABLE office_settings
column office_id (bigint not null)
column setting_value (NVARCHAR(500) NOT NULL)

office_id    setting_value
--------------------------------------------
4             0.2
4             0.2
4             2
4             3
5             0.2
5             0.2
5             2
5             3

Eu gostaria de converter isso usando o PIVOT (supondo que seja a ferramenta certa para o trabalho) para isso, basicamente "agrupando por" a coluna office_id, então:

office_id    value_1    value_2    value_3   value_4
------------------------------------------------------
    4         0.2         0.2         2         3
    5         0.2         0.2         2         3

Também devo mencionar que preciso CAST os valores da coluna setting_value para DECIMAL (4,2) no processo - caso isso tenha alguma relação com o T-SQL necessário para realizá-lo.

Alguma ideia?

sql-server pivot

1 respostas

  1. Best Answer

    Tente o seguinte:

    select office_id,
[1] as value_1, [2] as value_2, 
[3] as value_3, [4] as value_4
from
(
  select office_id, setting_value,
    row_number() over(partition by office_id order by office_id) rnk
  from office_settings
) d
pivot
(
  max(setting_value)
  for rnk in ([1], [2], [3], [4])
) piv;

    Ou sem usar PIVOT(gera o mesmo plano de execução):

    WITH Numbered AS
(
    SELECT 
        OS.office_id,
        OS.setting_value,
        row_num = ROW_NUMBER() OVER (
            -- Note: non-deterministic
            PARTITION BY OS.office_id 
            ORDER BY OS.office_id)
    FROM dbo.office_settings AS OS
)
SELECT
    N.office_id,
    value_1 = MAX(CASE WHEN N.row_num = 1 THEN N.setting_value ELSE NULL END),
    value_2 = MAX(CASE WHEN N.row_num = 2 THEN N.setting_value ELSE NULL END),
    value_3 = MAX(CASE WHEN N.row_num = 3 THEN N.setting_value ELSE NULL END),
    value_4 = MAX(CASE WHEN N.row_num = 4 THEN N.setting_value ELSE NULL END)
FROM Numbered AS N
GROUP BY
    N.office_id;

    Com sql dinâmico (útil quando o número de colunas dinâmicas varia em uma janela de pesquisa):

    declare @pivcols as varchar(200);

select  @pivcols = stuff((select distinct '],[' + cast(row_number() 
over(partition by office_id order by office_id) as varchar(10))
from office_settings
for xml path('')), 1, 2, '') + ']';

declare @pivquery varchar(500); 

set @pivquery = 'Select office_id, '+   
@pivcols +' 
from   
(select office_id, setting_value, 
 row_number() over(partition by office_id order by office_id) rnk
 from office_settings
) as d
PIVOT
(
max(setting_value)
for rnk in ( '+ @pivcols +' )
) AS pvt ';


EXECUTE(@pivquery);
    • 5

relate perguntas