Avatar of Cluskitt
Cluskitt
Flag for Portugal asked on

Unpivot a query

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')

Open in new window

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

Open in new window

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.
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Cluskitt

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Cluskitt

ASKER
Works perfectly. I just had to remove the order by clause, because the order it's in is important. Thanks a lot.
Lowfatspread

you can't guarantee the order unless you have an order by clause....

if the order is important then adjust the pivoted column names to include the sequence...
Cluskitt

ASKER
Really? I've tried a few times, changing the variables and the results all came in the same order. I suppose that, since the names aren't important, just the values, I could rename the columns to reflect the order, but, at first glance, it doesn't seem necessary.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Lowfatspread

it may well work as you require at present , i am only pointing out that this order is not guaranteed to always occur....

to have a specific order you always need an order by statement , otherwise you are getting a random sequence  (in effect what ever sequence suits the current best optimisation practice of the query compiler... table statistics, disk, db engine , hardware technology , and software patches could all influence that sequence... if you dont specify a sequence then the query takes no special interest in the order of the columns specified in the select / pivot/ unpivot instructions...)
Cluskitt

ASKER
I renamed the fields. Basically prefixed them with 01-, 02-, etc... seeing as the name isn't important, just the order, it's all good. Thanks once again.