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