Usuário com melhor resposta
Transformar diversas linhas em uma coluna

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 | 2000Tem 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
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
- Marcado como Resposta Leonardo N. D'Amato sexta-feira, 30 de agosto de 2013 14:50
-
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
- Marcado como Resposta Leonardo N. D'Amato sexta-feira, 30 de agosto de 2013 14:50
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
- Marcado como Resposta Leonardo N. D'Amato sexta-feira, 30 de agosto de 2013 14:50
-
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
-
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
- Marcado como Resposta Leonardo N. 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