SQLTeam.com | Weblogs | Forums

Required help for pivot query


#1

Dear all,
my table column is like below.

group Q1 Q2 Q3 Q4 Q5 Q6

IT 25 356 88 44 77 55
Software 552 88 777 77 11 21

need result as below

      IT       SOFTWARE

Q1 25 552
Q2 356 88
Q3 88 777
Q4 44 77
Q5 77 11
Q6 55 21

Can anyone help me.

Thanks

Rajnidas


#2
SELECT
	coalesce(A.field1,B.field2) AS myField
	,A.IT
	,B.Software
FROM 
	
(   SELECT field1,IT FROM dbo.SourceTable AS ST	
	UNPIVOT
	(IT FOR [field1] IN ([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])) as I
	WHERE [group] ='IT'
) A

FULL JOIN

(	SELECT field2,Software FROM dbo.SourceTable AS ST
	UNPIVOT
	(Software FOR [field2] IN ([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])) as S
	WHERE [group]='Software'
)B
ON 
A.field1=B.field2

Output:

myField   IT          Software
Q1        25          552
Q2        356         88
Q3        88          777
Q4        44          77
Q5        77          11
Q6        55          21

Other way to do it:

SELECT 
	coalesce(A.field,B.field) AS myField
	,A.IT
	,B.Software
FROM 
(
	SELECT *
	FROM dbo.sourceTable AS ST
	CROSS APPLY
	(	
		VALUES
		('Q1', ST.Q1),
		('Q2', ST.Q2),
		('Q3', ST.Q3),
		('Q4', ST.Q4),
		('Q5', ST.Q5),
		('Q6', ST.Q6)
	) c (field, IT)
	WHERE ST.[group] ='IT'
)A
FULL JOIN
(
	SELECT *
	FROM dbo.sourceTable AS ST
	CROSS APPLY
	(
	VALUES
		('Q1', ST.Q1),
		('Q2', ST.Q2),
		('Q3', ST.Q3),
		('Q4', ST.Q4),
		('Q5', ST.Q5),
		('Q6', ST.Q6)
	) c (field, Software)
	WHERE ST.[group] ='Software'
)B
ON A.field = B.field

#3

This also works:

select *
from dbo.STunpivot
unpivot 
    (
	   value for Q in ([Q1],[Q2],[Q3],[Q4],[Q5],[Q6])
    ) up
pivot 
    (
	   max(value) for mygroup in ([IT],[Software])
    ) p