Two operations in one Pivot query

Transact-SQL
1

I have data from a select query as follows:
ScopeGroupID Activity Col1 Col2 Col3
421471 Release to Trace 001-20-01 19072M-001-09-01 19101
421471 Trace Complete 001-20-02 19072M-001-09-02 19102
421471 NDE Complete 001-20-03 19072M-001-09-03 19103
421473 Release to Trace 001-20-04 19072M-001-09-04 19104
421473 Trace Complete 001-20-05 19072M-001-09-05 19105
421473 NDE Complete 001-20-06 19072M-001-09-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.
Thanks,

Sqlbug

2

welcome back. please provide this data as follows. help us help you

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

insert into @moonshine
select 421471, 'Release to Trace', '001-20-01', '19072M-001-09-01', 19101 union
select 421471, 'Trace Complete', '001-20-02', '19072M-001-09-02' 19102
3

Hi Yosi,
You did it already, I just added few more lines. Here is it, keeping yours:

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

insert into @moonshine
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

4

Did you test this locally and does it work?

5

Sorry, missing the commas:

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

insert into @moonshine
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

6

Something like this, even though it needed lot more tweaking for the dynamic list of column values being made into column headers and another dynamic list of columns being made into rows.

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];