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

DECLARE @Table1 TABLE([Desc] nvarchar(100),[Date] nvarchar(100),SM nvarchar(10),[R1] numeric(18,2), [R2] numeric(18,2), [R3] numeric(18,2));

INSERT INTO @Table1
    ([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
    [Desc], [Date],
    SUM([A-B]) As [Reading1 A-B],SUM([B-C]) As [Reading1 B-C],SUM([A-C]) As [Reading1 A-C],
    SUM([A-B1]) As [Reading2 A-B],SUM([B-C1]) As [Reading2 B-C],SUM([A-C1]) As [Reading2 A-C],
    SUM([A-B2]) As [Reading3 A-B],SUM([B-C2]) As [Reading3 B-C],SUM([A-C2]) As [Reading3 A-C]
FROM
(
  SELECT 
   [Desc],
   [Date],
   [SM] as Category,
   [SM]+'1' As Category1,
   [SM]+'2' As Category2,
   R1, 
   R2, 
   R3
  FROM @Table1
 ) AS P

 -- For Sales
 PIVOT
 (
   SUM(R1) FOR Category IN ([A-B], [B-C], [A-C])
 ) AS pv1

 -- For Stock
 PIVOT
 (
   SUM(R2) FOR Category1 IN ([A-B1], [B-C1], [A-C1])
 ) AS pv2

 -- For Target
 PIVOT
 (
   SUM(R3) FOR Category2 IN ([A-B2], [B-C2], [A-C2])
 ) AS pv3
 Group BY [Desc],[Date]
 GO

fork query

edited sep 26 23

created nov 18 16 anonymous