Transformar diversas linhas em uma coluna

Question

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

Answer 1

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: [e-mail]

Answer 2

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

Answer 3

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: [e-mail]

Answer 4

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

Answer 5

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: [e-mail]