troubleshooting Question

Unpivot a query

Avatar of Cluskitt
CluskittFlag for Portugal asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
6 Comments1 Solution712 ViewsLast Modified:
I have a long query that uses many CASE statements to provide different columns. This will generate 1 row with all the results. Basically, 2x34 (row+header, each 34 columns). I need to unpivot it, so 34 rows with 2 columns.
Query:
DECLARE @dIni varchar(8),@Emp int
SET @Emp=114
SET @dIni='20120701'

SELECT SUM(CASE 
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)<1
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=1
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '1-',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=1
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=2
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '1-2',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=2
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=3
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '2-3',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=3
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=4
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '3-4',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=4
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=5
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '4-5',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=5
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=6
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '5-6',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=6
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=7
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '6-7',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=7
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=8
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '7-8',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=8
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=9
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=10
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '8-10',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=10
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
					OR YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo) BETWEEN 11 AND 14
					OR (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=15
						AND MONTH(GETDATE())<=MONTH(psa_Dt_admissao_grupo)) THEN 1 ELSE 0
			END) '10-15',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)=15
						AND MONTH(GETDATE())>MONTH(psa_dt_admissao_grupo))
						OR YEAR(GETDATE())-YEAR(psa_dt_admissao_grupo)>15 THEN 1 ELSE 0
			END) '15+',
		SUM(CASE 
				WHEN YEAR(GETDATE())-YEAR(psa_dt_nascimento)<18
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=18
						AND MONTH(GETDATE())<=MONTH(psa_dt_nascimento)) THEN 1 ELSE 0
			END) '18-',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=18
						AND MONTH(GETDATE())>MONTH(psa_dt_nascimento))
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento) BETWEEN 19 AND 25)
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=26
						AND MONTH(GETDATE())<=MONTH(psa_dt_nascimento)) THEN 1 ELSE 0
			END) '18-25',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=26
						AND MONTH(GETDATE())>MONTH(psa_dt_nascimento))
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento) BETWEEN 27 AND 30)
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=31
						AND MONTH(GETDATE())<=MONTH(psa_dt_nascimento)) THEN 1 ELSE 0
			END) '26-30',
		SUM(CASE
 				WHEN (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=31
						AND MONTH(GETDATE())>MONTH(psa_dt_nascimento))
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento) BETWEEN 32 AND 40)
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=41
						AND MONTH(GETDATE())<=MONTH(psa_dt_nascimento)) THEN 1 ELSE 0
			END) '31-40',
		SUM(CASE
 				WHEN (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=41
						AND MONTH(GETDATE())>MONTH(psa_dt_nascimento))
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento) BETWEEN 42 AND 50)
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=51
						AND MONTH(GETDATE())<=MONTH(psa_dt_nascimento))  THEN 1 ELSE 0
			END) '41-50',
		SUM(CASE
				WHEN (YEAR(GETDATE())-YEAR(psa_dt_nascimento)=51
						AND MONTH(GETDATE())>MONTH(psa_dt_nascimento))
					OR (YEAR(GETDATE())-YEAR(psa_dt_nascimento)>51) THEN 1 ELSE 0
			END) '51+',
		SUM(CASE
				WHEN psa_pas_pais='AGO' THEN 1 ELSE 0
			END) 'AGO',
		SUM(CASE
				WHEN psa_pas_pais='CPV' THEN 1 ELSE 0
			END) 'CPV',
		SUM(CASE
				WHEN psa_pas_pais IN ('GNB','GIN') THEN 1 ELSE 0
			END) 'GNB',
		SUM(CASE
				WHEN psa_pas_pais='BRA' THEN 1 ELSE 0
			END) 'BRA',
		SUM(CASE
				WHEN psa_pas_pais='MOZ' THEN 1 ELSE 0
			END) 'MOZ',
		SUM(CASE
				WHEN psa_pas_pais='UKR' THEN 1 ELSE 0
			END) 'UKR',
		SUM(CASE
				WHEN psa_pas_pais='RUS' THEN 1 ELSE 0
			END) 'RUS',
		SUM(CASE
				WHEN psa_pas_pais='ROM' THEN 1 ELSE 0
			END) 'ROM',
		SUM(CASE
				WHEN psa_pas_pais NOT IN ('AGO','CPV','GNB','GIN','BRA','MOZ','UKR','RUS','ROM','PRT') THEN 1 ELSE 0
			END) 'OUT',
		SUM(CASE
				WHEN hbl_habilitacao_literaria < 221 THEN 1 ELSE 0
			END) '4 Cl',
		SUM(CASE
				WHEN hbl_habilitacao_literaria BETWEEN 221 AND 222 THEN 1 ELSE 0
			END) '6 Cl',
		SUM(CASE
				WHEN hbl_habilitacao_literaria BETWEEN 231 AND 234 THEN 1 ELSE 0
			END) '9 Cl',
		SUM(CASE
				WHEN hbl_habilitacao_literaria BETWEEN 311 AND 499 THEN 1 ELSE 0
			END) '12 Cl',
		SUM(CASE
				WHEN hbl_habilitacao_literaria BETWEEN 500 AND 599 THEN 1 ELSE 0
			END) 'Bac',
		SUM(CASE
				WHEN hbl_habilitacao_literaria BETWEEN 600 AND 699 THEN 1 ELSE 0
			END) 'Lic',
		SUM(CASE
				WHEN hbl_habilitacao_literaria BETWEEN 700 AND 799 THEN 1 ELSE 0
			END) 'Mes',
		SUM(CASE
				WHEN hbl_habilitacao_literaria BETWEEN 800 AND 899 THEN 1 ELSE 0
			END) 'Dou'
FROM (rh_tb2t0 t1 
	INNER JOIN (SELECT tb2_emp_empresa emp, 
					tb2_num_empregado num, 
					MAX(tb2_dt_efectiva) md 
				FROM rh_tb2t0 
				WHERE tb2_dt_Efectiva<@dIni 
				GROUP BY tb2_emp_Empresa, 
					tb2_num_Empregado) t2 
	ON tb2_emp_empresa=emp 
		AND tb2_num_empregado=num 
		AND tb2_dt_Efectiva=md)
INNER JOIN (rh_trbt0 r1 
	INNER JOIN (SELECT trb_emp_empresa em, 
					trb_num_empregado nu, 
					MAX(trb_dt_efectiva) mx 
				FROM rh_trbt0 
				WHERE trb_dt_Efectiva<@dIni 
				GROUP BY trb_emp_empresa, 
						trb_num_empregado) r2 
	ON trb_emp_Empresa=em 
		AND trb_num_empregado=nu 
		AND trb_dt_efectiva=mx)
ON trb_emp_empresa=tb2_emp_empresa
	AND trb_num_empregado=tb2_num_Empregado
INNER JOIN (rh_cntt0 c1 
	INNER JOIN (SELECT cnt_emp_empresa empc, 
					cnt_num_empregado numc, 
					MAX(cnt_dt_historico) mdc 
				FROM rh_cntt0 
				WHERE cnt_dt_historico<@dIni 
				GROUP BY cnt_emp_Empresa, 
						cnt_num_empregado) c2 
	ON cnt_emp_Empresa=empc 
		AND cnt_num_Empregado=numc 
		AND cnt_dt_historico=mdc)
ON cnt_emp_empresa=trb_emp_Empresa
	AND cnt_num_Empregado=trb_num_empregado
INNER JOIN rh_psat0
ON psa_emp_Empresa=cnt_emp_Empresa
	AND psa_num_Empregado=cnt_num_empregado
INNER JOIN rh_hblt0
ON hbl_emp_Empresa=psa_Emp_Empresa
	AND hbl_num_Empregado=psa_num_empregado
INNER JOIN rh_catt0
ON tb2_cat_cod_categorias=cat_cod_categorias
WHERE trb_loc_cod_local=@Emp
	AND cnt_arz_cod_accao NOT IN ('DMS','FCN')
	AND cat_designacao NOT IN ('SOCIO GERENTE','ASSISTENTE DIRECAO','DIR. RESTAURANTE','DIRETOR')
Results (if you paste them on Excel or similar you might see them all aligned):
1-	1-2	2-3	3-4	4-5	5-6	6-7	7-8	8-10	10-15	15+	18-	18-25	26-30	31-40	41-50	51+	AGO	CPV	GNB	BRA	MOZ	UKR	RUS	ROM	OUT	4 Cl	6 Cl	9 Cl	12 Cl	Bac	Lic	Mes	Dou
25	4	3	4	3	0	0	1	2	1	0	0	33	6	4	0	0	0	2	0	0	0	0	0	0	0	0	2	7	31	0	3	0	0
I never could get the hang of pivot or unpivot. Not sure if this is easy or not cause of all the joins and cases.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros