none
Transformar diversas linhas em uma coluna RRS feed

  • Pergunta

  • Pessoal, bom dia. 

    Estou com um problema com uma tabela do meu banco.

    Quando criaram essa tabela, criaram assim: id | cargo | salario. Quando o cargo tem step (jr, pl, sr), eles criam um novo registro. Então minha tabela fica assim:

    1 | Programador Jr | 1200
    2 | Programador Pl | 1500
    3 | Programador Sr | 2000

    Tem como a partir dessa tabela, eu localizar todo mundo que começa com 'Programador', e cada registro que eu achar, transformar em coluna?

    O resultado que eu quero é esse:

    id |         cargo           |    Jr     |    Pl     |     Sr     |
    1  |    Programador    |  1200 |   1500 |   2000   |

    Obrigado :)


    Leonardo D'Amato

    sexta-feira, 30 de agosto de 2013 12:40

Respostas

  • Leonardo,

    Segue abaixo uma possibilidade de implementação.

    --Table Creation
    DECLARE @t1 TABLE
    (
    	Id		INT IDENTITY(1,1) PRIMARY KEY,
    	Cargo	VARCHAR(100),
    	Salario	DECIMAL(18,2)
    )
    
    --Data load
    INSERT INTO @t1(Cargo, Salario) VALUES
    ('Programador Jr', 1200),
    ('Programador Pl', 1500),
    ('Programador Sr', 2000),
    ('DBA Pl', 2500),
    ('DBA Sr', 2700),
    ('Analista comercial', 3000)
    
    --Final select
    SELECT
    	TAB1.Cargo,
    	TAB1.Salario_Fixo,
    	(SELECT b.Salario FROM @t1 b WHERE b.Cargo LIKE '%Jr%' AND REVERSE(SUBSTRING(REVERSE(b.Cargo), 3, 8000)) LIKE TAB1.Cargo) As Salario_Junior,
    	(SELECT b.Salario FROM @t1 b WHERE b.Cargo LIKE '%Pl%' AND REVERSE(SUBSTRING(REVERSE(b.Cargo), 3, 8000)) LIKE TAB1.Cargo) As Salario_Pleno,
    	(SELECT b.Salario FROM @t1 b WHERE b.Cargo LIKE '%Sr%' AND REVERSE(SUBSTRING(REVERSE(b.Cargo), 3, 8000)) LIKE TAB1.Cargo) As Salario_Senior
    FROM
    (
    	SELECT DISTINCT
    		CASE
    			WHEN a.Cargo NOT LIKE '%Jr%' AND a.Cargo NOT LIKE '%Pl%' AND a.Cargo NOT LIKE '%Sr%'
    				THEN a.Cargo
    			ELSE REVERSE(SUBSTRING(REVERSE(a.Cargo), 3, 8000))
    		END As Cargo,
    		CASE
    			WHEN a.Cargo NOT LIKE '%Jr%' AND a.Cargo NOT LIKE '%Pl%' AND a.Cargo NOT LIKE '%Sr%'
    				THEN a.Salario
    			ELSE NULL
    		END As Salario_Fixo
    	FROM @t1 a
    ) TAB1


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    sexta-feira, 30 de agosto de 2013 13:03
    Moderador
  • Leonardo,

    Segue uma nova possivel solução:

    --Table Creation
    DECLARE @t1 TABLE
    (
    	Id		INT IDENTITY(1,1) PRIMARY KEY,
    	Cargo	VARCHAR(100),
    	Salario	DECIMAL(18,2)
    )
    
    --Data load
    INSERT INTO @t1(Cargo, Salario) VALUES
    ('Programador', 1200),
    ('Programador I', 1200),
    ('Programador II', 1500),
    ('Programador III', 2000),
    ('Programador*', 2000),
    ('DBA I', 2500),
    ('DBA II', 2700),
    ('Analista comercial', 3000)
    
    --Final select
    SELECT
    	*
    FROM
    (
    	SELECT
    		REPLACE(REPLACE(REPLACE(REPLACE(a.Cargo, ' III', ''), ' II', ''), ' I', ''), '*', '') As Cargo,
    		a.Salario,
    		CASE
    			WHEN a.Cargo NOT LIKE '%*' AND a.Cargo NOT LIKE '% I' AND a.Cargo NOT LIKE '% II' AND a.Cargo NOT LIKE '% III'
    				THEN 'Nada'
    			WHEN a.Cargo LIKE '%*'
    				THEN '*'
    			ELSE REVERSE(SUBSTRING(REVERSE(a.Cargo), 0, CHARINDEX(' ', REVERSE(a.Cargo))))
    		END As Nivel
    	FROM @t1 a
    ) TAB1
    PIVOT
    (
    	MAX(Salario) FOR Nivel IN ([Nada], [I], [II], [III], [*])
    ) PVT


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    sexta-feira, 30 de agosto de 2013 14:34
    Moderador

Todas as Respostas

  • Leonardo,

    Segue abaixo uma possibilidade de implementação.

    --Table Creation
    DECLARE @t1 TABLE
    (
    	Id		INT IDENTITY(1,1) PRIMARY KEY,
    	Cargo	VARCHAR(100),
    	Salario	DECIMAL(18,2)
    )
    
    --Data load
    INSERT INTO @t1(Cargo, Salario) VALUES
    ('Programador Jr', 1200),
    ('Programador Pl', 1500),
    ('Programador Sr', 2000),
    ('DBA Pl', 2500),
    ('DBA Sr', 2700),
    ('Analista comercial', 3000)
    
    --Final select
    SELECT
    	TAB1.Cargo,
    	TAB1.Salario_Fixo,
    	(SELECT b.Salario FROM @t1 b WHERE b.Cargo LIKE '%Jr%' AND REVERSE(SUBSTRING(REVERSE(b.Cargo), 3, 8000)) LIKE TAB1.Cargo) As Salario_Junior,
    	(SELECT b.Salario FROM @t1 b WHERE b.Cargo LIKE '%Pl%' AND REVERSE(SUBSTRING(REVERSE(b.Cargo), 3, 8000)) LIKE TAB1.Cargo) As Salario_Pleno,
    	(SELECT b.Salario FROM @t1 b WHERE b.Cargo LIKE '%Sr%' AND REVERSE(SUBSTRING(REVERSE(b.Cargo), 3, 8000)) LIKE TAB1.Cargo) As Salario_Senior
    FROM
    (
    	SELECT DISTINCT
    		CASE
    			WHEN a.Cargo NOT LIKE '%Jr%' AND a.Cargo NOT LIKE '%Pl%' AND a.Cargo NOT LIKE '%Sr%'
    				THEN a.Cargo
    			ELSE REVERSE(SUBSTRING(REVERSE(a.Cargo), 3, 8000))
    		END As Cargo,
    		CASE
    			WHEN a.Cargo NOT LIKE '%Jr%' AND a.Cargo NOT LIKE '%Pl%' AND a.Cargo NOT LIKE '%Sr%'
    				THEN a.Salario
    			ELSE NULL
    		END As Salario_Fixo
    	FROM @t1 a
    ) TAB1


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    sexta-feira, 30 de agosto de 2013 13:03
    Moderador
  • Fabrizzio, deu certo esta parte, muito obrigado. Porém, em outro banco de dados, preciso fazer a mesma coisa, mas com o cenario um pouco diferente. Tentei fazer como você me passou nessa outra, mas não está dando certo. Veja:

    Nesse outro caso, cada cargo tem 5 casos diferentes:

    Programador
    Programador I
    Programador II
    Programador III
    Programador*

    Tentei fazer de acordo com o seu código, mas dei com os burros nágua rs. 

    Obrigado pela ajuda!


    Leonardo D'Amato

    sexta-feira, 30 de agosto de 2013 13:57
  • Leonardo,

    Segue uma nova possivel solução:

    --Table Creation
    DECLARE @t1 TABLE
    (
    	Id		INT IDENTITY(1,1) PRIMARY KEY,
    	Cargo	VARCHAR(100),
    	Salario	DECIMAL(18,2)
    )
    
    --Data load
    INSERT INTO @t1(Cargo, Salario) VALUES
    ('Programador', 1200),
    ('Programador I', 1200),
    ('Programador II', 1500),
    ('Programador III', 2000),
    ('Programador*', 2000),
    ('DBA I', 2500),
    ('DBA II', 2700),
    ('Analista comercial', 3000)
    
    --Final select
    SELECT
    	*
    FROM
    (
    	SELECT
    		REPLACE(REPLACE(REPLACE(REPLACE(a.Cargo, ' III', ''), ' II', ''), ' I', ''), '*', '') As Cargo,
    		a.Salario,
    		CASE
    			WHEN a.Cargo NOT LIKE '%*' AND a.Cargo NOT LIKE '% I' AND a.Cargo NOT LIKE '% II' AND a.Cargo NOT LIKE '% III'
    				THEN 'Nada'
    			WHEN a.Cargo LIKE '%*'
    				THEN '*'
    			ELSE REVERSE(SUBSTRING(REVERSE(a.Cargo), 0, CHARINDEX(' ', REVERSE(a.Cargo))))
    		END As Nivel
    	FROM @t1 a
    ) TAB1
    PIVOT
    (
    	MAX(Salario) FOR Nivel IN ([Nada], [I], [II], [III], [*])
    ) PVT


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    sexta-feira, 30 de agosto de 2013 14:34
    Moderador
  • Simplesmente perfeito, Fabrizzio. 

    Fiquei com uma dúvida aqui: 'Nada' e '*'  vc nomeou dentro da subquery, mas e os steps I, II e III, onde você o nomeou para fazer o Pivot?


    Leonardo D'Amato

    sexta-feira, 30 de agosto de 2013 14:50
  • Leonardo,

    Os steps I, II e III vieram de uma transformação pelo comando: 

    REVERSE(SUBSTRING(REVERSE(a.Cargo), 0, CHARINDEX(' ', REVERSE(a.Cargo))))

    Porem, utilizando essa logica onde não havia nada ou * daria erro, por isso que antes de aplicar a transformação foi preciso remove-los da logica levando em consideração que o case será aplicado para o primeiro sucesso, ou seja:

    CASE

    WHEN 1=1 THEN 'A'

    WHEN 1=1 THEN 'B'

    O retorno será sempre A


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    sexta-feira, 30 de agosto de 2013 15:03
    Moderador