Usuário com melhor resposta
transformar resultados de linha para coluna

Pergunta
-
Seri que existe a instrução PIVOT do sql que aliás já usei algumas vezes mas não vejo uma forma de usa-la neste caso.
Tenho uma consulta que retorna uma quantidade de clientes agrupada por estado (UF). A instrução usa um COUNT(codCliente) e UF ficando assim
Quantidade | UF
1345 | SP
323 | RJ
e assim por diante para todos os Estados
Agora preciso fazer com que esses nomes de Estados se tornem nomes das colunas ficando os registros na linha com a quantidade respectiva de cada coluna. Isto é:
SP | RJ
1345 | 323
Se o Estado não tiver nenhum registro tanto faz retornar um zero na sua coluna quanto não retornar nada. Isso não é importante.
Respostas
-
Boa noite spitzmann, segue exemplo de pivot, conforme os dados informados.
-- Criando tab temporária (cte) com os dados ;with cte (codCliente, UF) as ( SELECT 1 , 'SP' UNION SELECT 1 , 'RJ' UNION SELECT NULL , 'MG' UNION ALL SELECT codCliente+1, UF FROM CTE WHERE (codCliente < 1345 AND UF = 'SP') OR (codCliente < 323 AND UF = 'RJ') ) -- Executando select com PIVOT SELECT [SP],[RJ],[MG] FROM ( SELECT ISNULL(COUNT(codCliente),0) as QTD, UF FROM cte GROUP BY UF ) as X PIVOT ( MAX(qtd) FOR UF IN ([SP],[RJ],[MG]) ) AS pvt OPTION (maxrecursion 0) --> utilizado por causa do CTE
Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino- Sugerido como Resposta Leonardo Marcelino segunda-feira, 14 de dezembro de 2009 23:43
- Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
-
sptizman,
Acredito que o Pivot poderia se encaixar, bem como, um CTE.
Veja este exemplo:
CREATE
TABLE CLIENTES
(CODIGO INT IDENTITY(1,1),
NOME
VARCHAR(20),
UF
CHAR(2) DEFAULT 'SP')
INSERT INTO CLIENTES VALUES ('PEDRO','SP')
INSERT INTO CLIENTES VALUES ('JUNIOR','SP')
INSERT INTO CLIENTES VALUES ('ANTONIO','SP')
INSERT INTO CLIENTES VALUES ('SILVA','RJ')
INSERT INTO CLIENTES VALUES ('SILVIO','RJ')
INSERT INTO CLIENTES VALUES ('ANDRE','AC')
INSERT INTO CLIENTES VALUES ('FERNANDA','MG')
INSERT INTO CLIENTES VALUES ('FERNANDO','MG')
SELECT * FROM CLIENTES
;WITH Estados (SP, RJ, MG, AC)
AS
(
Select Distinct
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='SP') AS SP,
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='MG') AS MG,
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='RJ') AS RJ,
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='AC') AS AC
From Clientes
)
SELECT
* FROM Estados
Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA- Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
-
Aproveitando os dados do Junior Galvão, segue tb outro exemplo q simula a função pivot.
antes do sql 2005 esta era uma das formas de simula-lo.
DECLARE @CLIENTES TABLE (CODIGO INT IDENTITY(1,1), NOME VARCHAR(20), UF CHAR(2) DEFAULT 'SP' ) INSERT INTO @CLIENTES VALUES ('PEDRO','SP') INSERT INTO @CLIENTES VALUES ('JUNIOR','SP') INSERT INTO @CLIENTES VALUES ('ANTONIO','SP') INSERT INTO @CLIENTES VALUES ('SILVA','RJ') INSERT INTO @CLIENTES VALUES ('SILVIO','RJ') INSERT INTO @CLIENTES VALUES ('ANDRE','AC') INSERT INTO @CLIENTES VALUES ('FERNANDA','MG') INSERT INTO @CLIENTES VALUES ('FERNANDO','MG') SELECT COUNT(NOME) AS QTD, UF FROM @CLIENTES GROUP BY UF SELECT SP = COUNT(CASE UF WHEN 'SP' THEN NOME END), RJ = COUNT(CASE UF WHEN 'RJ' THEN NOME END), MG = COUNT(CASE UF WHEN 'MG' THEN NOME END), AC = COUNT(CASE UF WHEN 'AC' THEN NOME END) FROM @CLIENTES
Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino- Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
Todas as Respostas
-
Boa noite spitzmann, segue exemplo de pivot, conforme os dados informados.
-- Criando tab temporária (cte) com os dados ;with cte (codCliente, UF) as ( SELECT 1 , 'SP' UNION SELECT 1 , 'RJ' UNION SELECT NULL , 'MG' UNION ALL SELECT codCliente+1, UF FROM CTE WHERE (codCliente < 1345 AND UF = 'SP') OR (codCliente < 323 AND UF = 'RJ') ) -- Executando select com PIVOT SELECT [SP],[RJ],[MG] FROM ( SELECT ISNULL(COUNT(codCliente),0) as QTD, UF FROM cte GROUP BY UF ) as X PIVOT ( MAX(qtd) FOR UF IN ([SP],[RJ],[MG]) ) AS pvt OPTION (maxrecursion 0) --> utilizado por causa do CTE
Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino- Sugerido como Resposta Leonardo Marcelino segunda-feira, 14 de dezembro de 2009 23:43
- Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
-
sptizman,
Acredito que o Pivot poderia se encaixar, bem como, um CTE.
Veja este exemplo:
CREATE
TABLE CLIENTES
(CODIGO INT IDENTITY(1,1),
NOME
VARCHAR(20),
UF
CHAR(2) DEFAULT 'SP')
INSERT INTO CLIENTES VALUES ('PEDRO','SP')
INSERT INTO CLIENTES VALUES ('JUNIOR','SP')
INSERT INTO CLIENTES VALUES ('ANTONIO','SP')
INSERT INTO CLIENTES VALUES ('SILVA','RJ')
INSERT INTO CLIENTES VALUES ('SILVIO','RJ')
INSERT INTO CLIENTES VALUES ('ANDRE','AC')
INSERT INTO CLIENTES VALUES ('FERNANDA','MG')
INSERT INTO CLIENTES VALUES ('FERNANDO','MG')
SELECT * FROM CLIENTES
;WITH Estados (SP, RJ, MG, AC)
AS
(
Select Distinct
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='SP') AS SP,
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='MG') AS MG,
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='RJ') AS RJ,
(SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='AC') AS AC
From Clientes
)
SELECT
* FROM Estados
Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA- Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
-
Aproveitando os dados do Junior Galvão, segue tb outro exemplo q simula a função pivot.
antes do sql 2005 esta era uma das formas de simula-lo.
DECLARE @CLIENTES TABLE (CODIGO INT IDENTITY(1,1), NOME VARCHAR(20), UF CHAR(2) DEFAULT 'SP' ) INSERT INTO @CLIENTES VALUES ('PEDRO','SP') INSERT INTO @CLIENTES VALUES ('JUNIOR','SP') INSERT INTO @CLIENTES VALUES ('ANTONIO','SP') INSERT INTO @CLIENTES VALUES ('SILVA','RJ') INSERT INTO @CLIENTES VALUES ('SILVIO','RJ') INSERT INTO @CLIENTES VALUES ('ANDRE','AC') INSERT INTO @CLIENTES VALUES ('FERNANDA','MG') INSERT INTO @CLIENTES VALUES ('FERNANDO','MG') SELECT COUNT(NOME) AS QTD, UF FROM @CLIENTES GROUP BY UF SELECT SP = COUNT(CASE UF WHEN 'SP' THEN NOME END), RJ = COUNT(CASE UF WHEN 'RJ' THEN NOME END), MG = COUNT(CASE UF WHEN 'MG' THEN NOME END), AC = COUNT(CASE UF WHEN 'AC' THEN NOME END) FROM @CLIENTES
Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino- Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
-
-
Bom Dia,
Em complemento às soluções postadas, eu sugiro efetuar uma pesquisa prévia, pois, certamente você encontrará muitas soluções já que é uma dúvida bastante recorrente. Se desejar tornar as coisas um pouco mais dinâmicas, segue um exemplo:CREATE TABLE CLIENTES ( CODIGO INT IDENTITY(1,1), NOME VARCHAR(20), UF CHAR(2) DEFAULT 'SP') INSERT INTO CLIENTES VALUES ('PEDRO','SP') INSERT INTO CLIENTES VALUES ('JUNIOR','SP') INSERT INTO CLIENTES VALUES ('ANTONIO','SP') INSERT INTO CLIENTES VALUES ('SILVA','RJ') INSERT INTO CLIENTES VALUES ('SILVIO','RJ') INSERT INTO CLIENTES VALUES ('ANDRE','AC') INSERT INTO CLIENTES VALUES ('FERNANDA','MG') INSERT INTO CLIENTES VALUES ('FERNANDO','MG') DECLARE @QTD INT, @i INT, @cmdSQL VARCHAR(8000), @Sufixo VARCHAR(100), @UF CHAR(2) SET @QTD = (SELECT COUNT(DISTINCT UF) FROM CLIENTES) SET @i = 1 SET @cmdSQL = 'SELECT COUNT(*) As Total ' SET @Sufixo = CHAR(10) + ' ,[?] = COUNT(CASE UF WHEN ''?'' THEN CODIGO END)' SET @UF = '' WHILE @i <= @QTD BEGIN SET @UF = (SELECT TOP (1) UF FROM CLIENTES WHERE UF > @UF ORDER BY UF) SET @cmdSQL = @cmdSQL + REPLACE(@Sufixo,'?',@UF) SET @i = @i + 1 END SET @cmdSQL = @cmdSQL + CHAR(10) + 'FROM CLIENTES' EXEC(@cmdSQL) PRINT @cmdSQL
Maiores detalhes em:
Transformando Linhas em Colunas com o SQL Server 2005
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!178.entryPivoteando, Despivoteando, Transpondo, Invertendo Colunas e Linhas no SQL Server
http://cid-f4f5c630410b9865.spaces.live.com/blog/cns!F4F5C630410B9865!629.entry
[ ]s,
Gustavo Maia Aguiar
http://gustavomaiaaguiar.spaces.live.com
SQL Server Saturday Night
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!878.entry
Classifique as respostas. O seu feedback é imprescindível- Sugerido como Resposta Gustavo Maia Aguiar terça-feira, 15 de dezembro de 2009 11:59
-
-