Two operations in one Pivot query

Question

I have data from a select query as follows:

ScopeGroupID             Activity                Col1         Col2               Col3
421471                   Release to Trace        001-20-01    19072M-001-01      19101
421471                   Trace Complete          001-20-02    19072M-001-02      19102
421471                   NDE Complete            001-20-03    19072M-001-03      19103
421473                   Release to Trace        001-20-04    19072M-001-04      19104
421473                   Trace Complete          001-20-05    19072M-001-05      19105
421473                   NDE Complete            001-20-06    19072M-001-06      19106

I am trying to achieve two things in one query as this will be a dynamic one with other things.

one: Get Activities as Column headers and two: Get Columns Col1, Col2 and Col3 to Come up as Row Values. Under each of the Column Headers created in step 1 should these corresponding values be displayed as follows:

ScopeGroupID       Selected Params      Release to Trace     Trace Complete       NDE Complete
421471             Col1                 001-20-01            001-20-02            001-20-03
421471             Col2                 19072M-001-01        19072M-001-02        19072M-001-03
421471             Col3                 19101                19102                19103
421473             Col1                 001-20-04            001-20-05            001-20-06
421473             Col2                 19072M-001-04        19072M-001-05        19072M-001-06
421473             Col3                 19104                19105                19106

The Activities are dynamic and the number can be many.

Any help will be very much appreciated. Here's the SQL Insert for testing:

declare @datatemp table(ScopeGroupID int, Activity varchar(50),
Col1 varchar(50), Col2 varchar(50), Col3 int)

insert into @datatemp
select 421471, 'Release to Trace', '001-20-01', '19072M-001-01', 19101
union
select 421471, 'Trace Complete', '001-20-02', '19072M-001-02', 19102
union
select 421471, 'NDE Complete', '001-20-03', '19072M-001-03', 19103
union
select 421473, 'Release to Trace', '001-20-04', '19072M-001-04', 19104
union
select 421473, 'Trace Complete', '001-20-05', '19072M-001-05', 19105
union
select 421473, 'NDE Complete', '001-20-06', '19072M-001-06', 19106

Answer 1

You first need to perform UNPIVOT, then PIVOT:

SELECT ScopeGroupID
      ,[column]
      ,[Release to Trace]
      ,[Trace Complete]
      ,[NDE Complete]
FROM
(
    SELECT ScopeGroupID
          ,Activity
          ,Col1
          ,Col2
          ,CAST(Col3 AS VARCHAR(50)) AS Col3
    FROM @datatemp
) DS
UNPIVOT
(
    [value] FOR [column] IN ([Col1], [Col2], [Col3])
) UNPVT
PIVOT
(
    MAX([value]) FOR [Activity] IN ([NDE Complete], [Release to Trace], [Trace Complete])
) PVT
ORDER BY ScopeGroupID
        ,[column];