SQL Server: colunas em linhas
Procurando por uma solução elegante (ou qualquer) para converter colunas em linhas.
Aqui está um exemplo: Eu tenho uma tabela com o seguinte esquema:
[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]
Aqui está o que eu quero obter como resultado:
[ID] [EntityId] [IndicatorName] [IndicatorValue]
E os valores do resultado serão:
1 1 'Indicator1' 'Value of Indicator 1 for entity 1'
2 1 'Indicator2' 'Value of Indicator 2 for entity 1'
3 1 'Indicator3' 'Value of Indicator 3 for entity 1'
4 2 'Indicator1' 'Value of Indicator 1 for entity 2'
Isso faz sentido? Você tem alguma sugestão sobre onde procurar e como fazê-lo no T-SQL?
sql sql-server tsql unpivot
Você já analisou o Pivot / Unpivot ?
— Josh Jay
No final, foi com a solução da bluefeet. Elegante e funcional. Muito obrigado a todos.
Você pode usar a função UNPIVOT para converter as colunas em linhas:
Observe que os tipos de dados das colunas que você está desviando devem ser os mesmos, portanto, talvez você precise converter os tipos de dados antes de aplicar o desanexação.
Você também pode usar CROSS APPLYcom UNION ALL para converter as colunas:
select 'Indicator1', Indicator1 union all
select 'Indicator2', Indicator2 union all
select 'Indicator3', Indicator3 union all
select 'Indicator4', Indicator4
) c (indicatorname, indicatorvalue);
Dependendo da sua versão do SQL Server, você pode usar o CROSS APPLY com a cláusula VALUES:
('Indicator1', Indicator1),
('Indicator2', Indicator2),
('Indicator3', Indicator3),
('Indicator4', Indicator4)
Por fim, se você tiver 150 colunas para desarticular e não desejar codificar a consulta inteira, poderá gerar a instrução sql usando SQL dinâmico:
where C.table_name = 'yourtable' and
C.column_name like 'Indicator%'
= 'select id, entityId,
for indicatorname in ('+ @colsunpivot +')
Para quem quer mais porcas e parafusos sobre UNPIVOTe / vs. APPLY, esta publicação de Brad Schulz em 2010 (e as seguintes ) é (são) linda.
Mensagem 8167, nível 16, estado 1, linha 147 O tipo de coluna "blahblah" está em conflito com o tipo de outras colunas especificadas na lista UNPIVOT.
— JDeckham
@JDPeckham Se você tiver tipos de dados diferentes, precisará convertê-los para que tenham o mesmo tipo e comprimento antes de executar a remoção do pivô. Aqui está mais informações sobre isso .
o método xml tem uma falha porque falha ao remover códigos xml como & gt ;, & lt; e & amp ;. Além disso, o desempenho pode ser significativamente aprimorado reescrevendo da seguinte maneira: selecione @colsUnpivot = stuff ((selecione ',' + quotename (C.column_name) como [text ()] de information_schema.columns como C, onde C.table_name = 'yourtable' e C.column_name como 'Indicator%' para o caminho xml (''), type) .value ('text () [1]', 'nvarchar (max)'), 1, 1, '')
bem Se você tem 150 colunas, acho que UNPIVOT não é uma opção. Então você pode usar truque xml
F.C.value('.', 'nvarchar(max)') as IndicatorValue
from CTE1 as c
outer apply c.Data.nodes('row/@*') as F(C)
select * from CTE2 where IndicatorName like 'Indicator%'
Você também pode escrever SQL dinâmico, mas eu gosto mais de xml - para SQL dinâmico, você precisa ter permissões para selecionar dados diretamente da tabela e isso nem sempre é uma opção.
Como há uma grande chama nos comentários, acho que vou adicionar alguns prós e contras do xml / dynamic SQL. Vou tentar ser o mais objetivo possível e não mencionar elegância e feia. Se você tiver outros prós e contras, edite a resposta ou escreva nos comentários
não é tão rápido quanto o SQL dinâmico, testes aproximados me deram que o xml é cerca de 2,5 vezes mais lento que o dinâmico (foi uma consulta na tabela ~ 250000 linhas, portanto, essa estimativa não é exata). Você pode compará-lo você mesmo, se quiser, aqui está o exemplo do sqlfiddle , em 100000 linhas eram 29s (xml) vs 14s (dinâmico);
pode ser mais difícil de entender para pessoas não familiarizadas com o xpath;
é o mesmo escopo das suas outras consultas e isso pode ser muito útil. Alguns exemplos vêm à mente
você pode consultar insertede deletedtabelas dentro do seu gatilho (não é possível com a dinâmica);
o usuário não precisa ter permissões na seleção direta da tabela. O que quero dizer é que, se você armazenou a camada de procedimentos e o usuário tem permissões para executar sp, mas não possui permissões para consultar tabelas diretamente, você ainda pode usar essa consulta dentro do procedimento armazenado;
você pode consultar a variável de tabela que você preencheu em seu escopo (para passá-la para o SQL dinâmico, você deve torná-la tabela temporária ou criar um tipo e passá-la como parâmetro para o SQL dinâmico;
você pode fazer essa consulta dentro da função (escalar ou com valor de tabela). Não é possível usar SQL dinâmico dentro das funções;
— Roman Pekar
Quais dados você está selecionando com XML que não requer a seleção de dados da tabela?
Por exemplo, você pode optar por não conceder aos usuários permissões para selecionar dados de tabelas, mas apenas em procedimentos armazenados que trabalham com tabelas, para que eu possa selecionar xml dentro do procedimento, mas preciso usar algumas soluções alternativas para usar o SQL dinâmico
Se você deseja que seus usuários possam executar o código, é necessário dar a eles o acesso necessário para executar o código. Não crie requisitos que não existem para melhorar sua resposta (você também não precisa comentar sobre as respostas concorrentes para analisá-la - se eles encontraram essa resposta, também podem encontrar a sua).
Além disso, se sua justificativa para usar XML é que você pode colocá-lo em um procedimento armazenado para evitar o acesso direto à tabela, talvez o seu exemplo deva mostrar como colocá-lo em um procedimento armazenado e como conceder direitos a um usuário para que ele pode executá-lo sem ter acesso de leitura à tabela subjacente. Para mim, isso é escasso, porque a maioria das pessoas que escrevem consultas em uma tabela tem acesso de leitura à tabela.
Eu diria que uma diferença de 10x na duração importa, sim. E ~ 8.000 linhas não são "grandes quantidades de dados" - devemos ver o que acontece com 800.000 linhas?
Apenas para ajudar novos leitores, criei um exemplo para entender melhor a resposta da @ bluefeet sobre o UNPIVOT.
,indicatorname
,indicatorvalue
(1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),
(2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),
(3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),
(4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')
) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
— Dmyan
Eu precisava de uma solução para converter colunas em linhas no Microsoft SQL Server, sem saber os nomes das colunas (usadas no gatilho) e sem o sql dinâmico (o sql dinâmico é muito lento para ser usado em um gatilho).
Finalmente encontrei esta solução, que funciona bem:
insRowTbl.PK,
insRowTbl.Username,
attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
attr.insRow.value('.', 'nvarchar(max)') as FieldValue
i.ID as PK,
i.LastModifiedBy as Username,
convert(xml, (select i.* for xml raw)) as insRowCol
) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)
Como você pode ver, converto a linha em XML (subconsulta selecione i, * para xml raw, isso converte todas as colunas em uma coluna xml)
Então, eu CRUZO APLICAR uma função a cada atributo XML desta coluna, para obter uma linha por atributo.
No geral, isso converte colunas em linhas, sem saber os nomes das colunas e sem usar sql dinâmico. É rápido o suficiente para o meu propósito.
(Edit: Acabei de ver Roman Pekar responder acima, que está fazendo o mesmo. Eu usei o gatilho sql dinâmico com cursores primeiro, que era 10 a 100 vezes mais lento que esta solução, mas talvez tenha sido causado pelo cursor, não pelo De qualquer forma, esta solução é muito simples e universal, portanto é definitivamente uma opção).
Estou deixando este comentário neste local, porque quero referenciar esta explicação no meu post sobre o gatilho de auditoria completa, que você pode encontrar aqui: https://stackoverflow.com/a/43800286/4160788
— flack
DECLARE @TableName varchar(max)=NULL
SELECT @TableName=COALESCE(@TableName+',','')+t.TABLE_CATALOG+'.'+ t.TABLE_SCHEMA+'.'+o.Name
INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=o.name
AND OBJECTPROPERTY(o.id,'IsMSShipped') = 0
AND i.rowcnt >350
AND o.xtype !='TF'
Você pode obter uma lista de tabelas com contas> 350. Você pode ver na lista de soluções da tabela como linha.
— cunay
Só porque eu não vi isso mencionado.
Se houver mais de 2016, aqui está mais uma opção para dinamizar dinamicamente os dados sem realmente usar o Dynamic SQL.