SQL Pivot with two columns

Question

I am trying to arrange some columns to output to C#. I have the following query

SELECT * FROM
(SELECT SerialNum,TestStatus,WaveLength,ConEnd,IL,RL,Tester
  FROM [P1].[dbo].[QuP] WHERE SerialNum = 'gyy4444444') AS t1
  PIVOT (
        MAX(IL)
        FOR WaveLength IN ([1310IL],[1550IL],[1650IL],[1750IL])
  ) p1 

This works great for IL but what would I have to do to also pull RL so it was [1310IL],[1310RL][1550IL],[1550RL]

Answer 1

I had a query in my query store that was similar I tweaked it a bit. It seems relevant and perhaps it will help. I would like to point out that this type of data manipulation is much easier to work with in a reporting tool, however, it the dataset is very large unfolding the data can become a bottleneck in said tool. I did not know how you wanted to handle the case when a more than one tester was assigned the same wavelength and serial number so I just took the max of the two and separated them by IR and IL, you would easily just take the max of the two if it at all matters.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE ReportData(
  SerialNumber INT, 
  WaveLength NVARCHAR(100), 
  RL DECIMAL(18,14), 
  IL DECIMAL(18,14),
  TesterID INT
);

INSERT INTO ReportData (SerialNumber,  WaveLength, RL, IL, TesterID)
VALUES
    (1, '1310IL', 1.00, 100.00,1111),
    (1, '1550IL', 2.00, 200.00,1111),
    (1, '1650IL', 3.00, 300.00,1111),
    (1, '1750IL', 4.00, 400.00,1111),
    (1, '1750IL', 4.01, 401.00,2222),

    (1, '1310RL', 5.00, 500.00,4444),
    (1, '1550RL', 6.00, 600.00,4444),
    (1, '1650RL', 7.00, 700.00,4444),
    (1, '1750RL', 8.00, 800.00,4444),
    (1, '1750RL', 9.01, 900.01,5555),

    (2, '1310IL', 11.00, 1100.00,1111),
    (2, '1550IL', 12.00, 1200.00,1111),
    (2, '1650IL', 13.00, 1300.00,1111),
    (2, '1750IL', 14.00, 1400.00,1111),
    (2, '1750IL', 14.02, 1400.02,2222),

    (2, '1310RL', 15.00, 1500.00,4444),
    (2, '1550RL', 16.00, 1600.00,4444),
    (2, '1650RL', 17.00, 1700.00,4444),
    (2, '1750RL', 18.00, 1800.00,4444),
    (2, '1750RL', 19.02, 1900.02,5555)

Query 1:

WITH PivotIL AS
(
    SELECT 
        SerialNumber, 
        [1310IL]=MAX([1310IL]),[1550IL]=MAX([1550IL]), [1650IL]=MAX([1650IL]), [1750IL]=MAX([1750IL]),
        TesterID=MAX(TesterID)
    FROM 
        ReportData A
    PIVOT
    (
        MAX(IL)
        FOR WaveLength IN([1310IL],[1550IL],[1650IL],[1750IL])
    )AS B   
    GROUP BY 
        SerialNumber
),
PivotRL AS
(
    SELECT 
        SerialNumber, 
        [1310RL]=MAX([1310RL]),[1550RL]=MAX([1550RL]), [1650RL]=MAX([1650RL]), [1750RL]=MAX([1750RL]),
        TesterID=MAX(TesterID)
    FROM 
        ReportData A
    PIVOT
    (
        MAX(RL)
        FOR WaveLength IN([1310RL],[1550RL],[1650RL],[1750RL])
    )AS B   
    GROUP BY 
        SerialNumber
)
,CombinedWithoutFinalGrouping AS
(
    SELECT
        IL.SerialNumber,
        [1310IL],[1550IL],[1650IL],[1750IL],ILTesterID = IL.TesterID,
        [1310RL],[1550RL],[1650RL],[1750RL],IRTesterID = RL.TesterID
    FROM
        PivotIL IL
        INNER JOIN PivotRL RL ON IL.SerialNUmber = RL.SerialNumber
)

SELECT
    SerialNumber,
    [1310IL]=MAX([1310IL]),[1550IL]=MAX([1550IL]), [1650IL]=MAX([1650IL]), [1750IL]=MAX([1750IL]),MaxTesterIDIL=MAX(ILTesterID),
    [1310RL]=MAX([1310RL]),[1550RL]=MAX([1550RL]), [1650RL]=MAX([1650RL]), [1750RL]=MAX([1750RL]),MaxTesterIDRL=MAX(IRTesterID)
FROM
    CombinedWithoutFinalGrouping
GROUP BY
    SerialNumber

Results:

| SerialNumber | 1310IL | 1550IL | 1650IL |  1750IL | MaxTesterIDIL | 1310RL | 1550RL | 1650RL | 1750RL | MaxTesterIDRL |
|--------------|--------|--------|--------|---------|---------------|--------|--------|--------|--------|---------------|
|            1 |    100 |    200 |    300 |     401 |          5555 |      5 |      6 |      7 |   9.01 |          5555 |
|            2 |   1100 |   1200 |   1300 | 1400.02 |          5555 |     15 |     16 |     17 |  19.02 |          5555 |

Answer 2

I think following example might help you solve your case:

drop table if exists #taghits

create table #taghits (userid int, email varchar(20), tagname varchar(20), hits int, lastview date)

insert into #taghits select 1, 'email1@here.com', 'tag1', 3, '2020-03-24';
insert into #taghits select 2, 'email2@here.com', 'tag1', 1, '2020-03-17';
insert into #taghits select 2, 'email2@here.com', 'tag2', 1, '2020-03-18';
insert into #taghits select 3, 'email3@here.com', 'tag1', 2, '2020-03-25';
insert into #taghits select 3, 'email3@here.com', 'tag2', 5, '2020-03-28';

select * from #taghits;

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((select distinct ', 
            SUM(CASE WHEN tagname=''' + CAST(tagname as varchar(10)) + ''' THEN [hits] ELSE 0 END) AS [' + CAST(tagname as varchar(10)) + '_hits],
            MAX(CASE WHEN tagname=''' + CAST(tagname as varchar(10)) + ''' THEN [lastview] ELSE NULL END) AS [' + CAST(tagname as varchar(10)) + '_lastview]'
            /*---------------You can add other columns here similar to above--------------*/
            FROM #taghits 
            FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
SET @query = 'SELECT userid, email, ' + @Cols + '  FROM #taghits group by userid, email' 

print (@query)
exec(@query)

Please find the running code here.