0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

CREATE TABLE #yt
([Desc] nvarchar(100),[Date] nvarchar(100),SM nvarchar(10),[R1] numeric(18,2), [R2] numeric(18,2), [R3] numeric(18,2))
;
    
INSERT INTO #yt
    ([Desc], [Date],[SM], [R1],[R2],[R3])
VALUES
('ABCReading','2021-08-26 00:00:00.000','A-B',91.01,92.02,93.03),
('ABCReading','2021-08-26 00:00:00.000','B-C',91.02,92.01,93.03),
('ABCReading','2021-08-26 00:00:00.000','A-C',91.03,92.02,93.03),
('DEFReading','2021-08-27 00:00:00.000','A-B',91.04,92.02,93.03),
('DEFReading','2021-08-27 00:00:00.000','B-C',91.05,92.02,93.03),
('DEFReading','2021-08-27 00:00:00.000','A-C',91.06,92.02,93.03)
;


select *
from 
(
  select [Desc], [Date],[SM],[SM]+'1' as SM1, [R1],[R2]
  from #yt
) src
pivot
(
sum(R1)
  for [SM] in ([A-B], [B-C], [A-C])
) piv1
pivot
(
sum(R2)
  for [SM1] in ([A-B2], [B-C1], [A-C1])
) piv2

fork query

edited sep 26 23

created nov 18 16 anonymous