transformar resultados de linha para coluna

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Leonardo,

Com certeza o Case é um recurso fantástico, eu utilizo muito.

---

Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA

Answer 5

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.entry

Pivoteando, 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

Answer 6

Maia,

É verdade, este tipo de assunto já foi bem discutido.

Acho muito legal o seu exemplo.

---

Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA

Answer 7

 :)

---

Classifique as respostas. O seu feedback é imprescindível