SQL Query with multiple PIVOT

Question

I have 3 tables as Register, Revision, Issue. table structure and some datas are as follows,

CREATE TABLE [dbo].[Issue](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProjectID] [int] NULL,
    [Drawing ID] [int] NULL,
    [Revision ID] [int] NULL,
    [Issue Number] [int] NULL,
    [Weight1] [float] NULL,
    [Weight2] [float] NULL,
    [Weight3] [float] NULL,
 CONSTRAINT [PK_DrawingIssue] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Register](
    [ID] [int] NOT NULL,
    [ProjectID] [int] NULL,
    [Number] [nvarchar](255) NULL,
 CONSTRAINT [PK_Drawing_Register] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Revision](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProjectID] [int] NULL,
    [Drawing ID] [int] NULL,
    [Revision] [nvarchar](255) NULL,
    [SDate] [datetime] NULL,
    [EDate] [datetime] NULL,
 CONSTRAINT [PK_DrawingRevision] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Issue] ON 

GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (9, 23, 3, 5, 2, 12, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (10, 23, 4, 6, 9, NULL, 32, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (19, 23, 7, 12, 2, 24, NULL, 24)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (164, 23, 12, 61, 2, NULL, NULL, 42)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (165, 23, 13, 62, 1, 24, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (167, 23, 13, 62, 0, NULL, 42, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (168, 23, 13, 62, 2, NULL, 43, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (169, 23, 13, 64, 0, NULL, NULL, 24)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (170, 23, 13, 64, 1, NULL, 42, 42)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (171, 23, 13, 64, 3, NULL, 24, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (254, 23, 5, 86, 4, 24, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (256, 23, 13, 88, 2, 24, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (257, 23, 13, 89, 1, NULL, 24, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (258, 23, 13, 64, 2, 8, 7, 6)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (259, 23, 16, 91, 1, NULL, 4, 6)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (260, 23, 16, 91, 2, NULL, NULL, 4)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (297, 2, 17, 108, 1, NULL, 7, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (298, 2, 17, 108, 2, NULL, 68, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (299, 2, 17, 108, 6, 67, NULL, 86)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (300, 2, 17, 109, 2, NULL, 68, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (301, 2, 17, 109, 1, NULL, NULL, 68)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (302, 2, 17, 109, 3, 68, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (303, 2, 17, 110, 1, NULL, 86, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (304, 2, 17, 110, 2, 68, NULL, 68)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (305, 2, 17, 110, 7, NULL, 68, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (306, 2, 17, 110, 8, NULL, NULL, 68)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (307, 2, 17, 110, 6, NULL, 68, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (308, 23, 18, 111, 1, 68, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (309, 23, 19, 112, 1, NULL, 68, 8)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (310, 23, 20, 113, 1, NULL, 6, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (311, 23, 21, 114, 1, 3, NULL, 68)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (312, 23, 22, 115, 1, NULL, 5, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (313, 23, 23, 116, 1, NULL, 4, 34)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (314, 23, 24, 117, 1, 5, 46, 436)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (315, 23, 25, 118, 1, NULL, NULL, 6)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (316, 23, 26, 119, 1, 46, 45, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (317, 23, 27, 120, 1, NULL, 6, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (318, 23, 28, 121, 1, NULL, NULL, 4)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (319, 23, 29, 122, 1, NULL, 45, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (320, 23, 30, 123, 1, 36, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (321, 26, 31, 124, 1, NULL, 36, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (322, 26, 32, 125, 1, 36, NULL, 36)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (323, 26, 33, 126, 1, NULL, 36, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (324, 26, 34, 127, 1, NULL, NULL, 36)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (325, 26, 35, 128, 1, 36, NULL, 45)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (326, 2, 36, 129, 1, NULL, 36, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (327, 26, 37, 130, 1, NULL, NULL, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (328, 2, 38, 131, 1, NULL, 7, NULL)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number], [Weight1], [Weight2], [Weight3]) VALUES (329, 23, 39, 132, 1, NULL, 56, NULL)
GO
SET IDENTITY_INSERT [dbo].[Issue] OFF
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (3, 23, N'1')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (4, 23, N'7')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (7, 23, N'3333')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (12, 23, N'D1')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (13, 23, N'DT1')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (16, 23, N'Dwg1')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (17, 2, N'D1')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (18, 23, N'23')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (19, 23, N'983')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (20, 23, N'100')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (21, 23, N'11112')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (22, 23, N'555')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (23, 23, N'666666')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (24, 23, N'77')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (25, 23, N'88')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (26, 23, N'99')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (27, 23, N'2')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (28, 23, N'3')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (29, 23, N'4')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (30, 23, N'Dwg12345')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (31, 26, N'1')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (32, 26, N'2')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (33, 26, N'3')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (34, 26, N'4')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (35, 26, N'5')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (36, 2, N'DT123')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (37, 26, N'DTApr04')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (38, 2, N'DTApr05')
GO
INSERT [dbo].[Register] ([ID], [ProjectID], [Number]) VALUES (39, 23, N'DTA05')
GO
SET IDENTITY_INSERT [dbo].[Revision] ON 

GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (5, 23, 3, N'0', CAST(N'2017-04-21 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (6, 23, 4, N'0', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (12, 23, 7, N'3', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (61, 23, 12, N'0', NULL, CAST(N'2017-04-10 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (62, 23, 13, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-08 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (64, 23, 13, N'1', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (88, 23, 13, N'1', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (89, 23, 13, N'2', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (90, 23, 13, N'1', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (91, 23, 16, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (108, 2, 17, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (109, 2, 17, N'1', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (110, 2, 17, N'2', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (111, 23, 18, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (112, 23, 19, N'0', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (113, 23, 20, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (114, 23, 21, N'0', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (115, 23, 22, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (116, 23, 23, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (117, 23, 24, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (118, 23, 25, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (119, 23, 26, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (120, 23, 27, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (121, 23, 28, N'0', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (122, 23, 29, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (123, 23, 30, N'0', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (124, 26, 31, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (125, 26, 32, N'0', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (126, 26, 33, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (127, 26, 34, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (128, 26, 35, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (129, 2, 36, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (130, 26, 37, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (131, 2, 38, N'0', NULL, CAST(N'2017-04-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision], [SDate], [EDate]) VALUES (132, 23, 39, N'0', CAST(N'2017-04-18 00:00:00.000' AS DateTime), NULL)
GO
SET IDENTITY_INSERT [dbo].[Revision] OFF
GO

i need a query which will return one row per drawing with latest 4 issues which is grouped by desc of RevID ( Revision Table ) then desc by ISsue ID ( Issue Table ) it needs to select top 4 records for each drawings and all the four records are to be combined together with pivot as like,

DNo | ISsue1 | Issue2 | Issue3 | Issue4 | SDate1 | SDate2 | SDate3 | Sdate4 | EDate1 | EDate2 | EDate3 | EDate4 | Total1 | Total2 | Total3 | Total4

I have tried something as follows,

CREATE FUNCTION [dbo].[Dwg_logQuery_Test](@ProjectID int)
RETURNS TABLE
as
return
(
SELECT ID,Number,
       Issue1, 
       Issue2, 
       Issue3, 
       Issue4,     
       Tonnage1,
       Tonnage2,
       Tonnage3,
       Tonnage4,
       SubmittedDate1,
       SubmittedDate2,
       SubmittedDate3,
       SubmittedDate4,
       EORReturnDate1,
       EORReturnDate2,
       EORReturnDate3,
       EORReturnDate4,
       Revision1,
       Revision2,
       Revision3,
       Revision4
FROM   (SELECT ID, 
               [Issue Number], 
               [Number],               
               [SubmittedDate],
               [EORReturnDate],            
               Revision,
               [Tonnage],
               'Issue' + Cast(rn AS CHAR(10)) AS Issue,
               'Tonnage' + Cast (rn AS CHAR(10)) AS TonnageHeader,
               'SubmittedDate'+Cast (rn AS CHAR(10)) AS SubmittedDateHeader,
               'EORReturnDate'+Cast (rn AS CHAR(10)) AS EORReturnDateHeader,
               'Revision'+Cast (rn AS CHAR(10)) AS RevisionHeader
        FROM   (SELECT dwgReg.ID, 
                       dwgIssue.[Issue Number],                        
                       Row_number() 
                         OVER ( 
                           partition BY dwgIssue.[Drawing ID] 
                           ORDER BY dwgReg.[ID], dwgRev.[Revision] 
                         DESC, dwgIssue.[Issue Number] DESC) 
                               AS rn,                              
                               dwgRev.Revision,                            
                               isnull(dwgIssue.weight1,0)+isnull(dwgIssue.weight2,0)+isnull(dwgIssue.weight3,0) as Tonnage,
                               dwgRev.[SDate] AS 'SubmittedDate',
                               dwgRev.[EDate] AS 'EORReturnDate'
                               ,dwgReg.Number

                FROM   Issue dwgIssue
                       INNER JOIN Register dwgReg
                               ON dwgIssue.[Drawing ID] = dwgReg.ID
                       INNER JOIN Revision dwgRev
                               ON dwgRev.ID=dwgIssue.[Revision ID]
                                  AND dwgIssue.[Drawing ID]=dwgReg.ID                                                                 
                                   where dwgReg.ProjectID=@ProjectID)dwgIssue
        WHERE  rn <= 4)dwgReg 
       PIVOT ( Max([Issue Number]) 
             FOR Issue IN (Issue1, 
                             Issue2, 
                             Issue3, 
                             Issue4)) pv

                             PIVOT (
                             MAX([Tonnage])
                             FOR TonnageHeader IN (Tonnage1, Tonnage2, Tonnage3, Tonnage4 )
                             ) pv1

                             PIVOT ( Max([SubmittedDate])
                             FOR SubmittedDateHeader IN (SubmittedDate1,SubmittedDate2,SubmittedDate3,SubmittedDate4 )
                             ) pv2

                             PIVOT ( Max([EORReturnDate])
                             FOR EORReturnDateHeader IN (EORReturnDate1,EORReturnDate2,EORReturnDate3,EORReturnDate4 )
                             ) pv3

                             PIVOT ( Max([Revision])
                             FOR RevisionHeader IN (Revision1,Revision2,Revision3,Revision4 )
                             ) pv4       

                             )


GO

but i am failing in multiple PIVOT So please some help me to resolve this.

Thanks in Advance.

McNets · Accepted Answer · 2017-04-26 11:26:32Z

Does this cover your desired result?

DECLARE @ProjectID int = 23;

WITH DataSrc as
(
SELECT ID, [Issue Number] INumber, 
               [Number] NNumber,               
               [SubmittedDate],
               [EORReturnDate],            
               Revision,
               [Tonnage],
               'Issue' + Cast(rn AS CHAR(10)) AS Issue,
               'Tonnage' + Cast (rn AS CHAR(10)) AS TonnageHeader,
               'SubmittedDate'+Cast (rn AS CHAR(10)) AS SubmittedDateHeader,
               'EORReturnDate'+Cast (rn AS CHAR(10)) AS EORReturnDateHeader,
               'Revision'+Cast (rn AS CHAR(10)) AS RevisionHeader
        FROM   (SELECT dwgReg.ID, 
                       dwgIssue.[Issue Number],                        
                       Row_number() 
                         OVER ( 
                           partition BY dwgIssue.[Drawing ID] 
                           ORDER BY dwgReg.[ID], dwgRev.[Revision] 
                         DESC, dwgIssue.[Issue Number] DESC) 
                               AS rn,                              
                               dwgRev.Revision,                            
                               isnull(dwgIssue.weight1,0)+isnull(dwgIssue.weight2,0)+isnull(dwgIssue.weight3,0) as Tonnage,
                               dwgRev.[SDate] AS 'SubmittedDate',
                               dwgRev.[EDate] AS 'EORReturnDate'
                               ,dwgReg.Number

                FROM   Issue dwgIssue
                       INNER JOIN Register dwgReg
                               ON dwgIssue.[Drawing ID] = dwgReg.ID
                       INNER JOIN Revision dwgRev
                               ON dwgRev.ID=dwgIssue.[Revision ID]
                                  AND dwgIssue.[Drawing ID]=dwgReg.ID                                                                 
                                   where dwgReg.ProjectID=@ProjectID)dwgIssue
        WHERE  rn <= 4
)
 , DataSrc2 as
 (
     SELECT NNumber, INumber, Issue, Tonnage, TonnageHeader, SubmittedDate, SubmittedDateHeader,Revision, RevisionHeader
     FROM  DataSrc
 )
SELECT * from DataSrc2
PIVOT  (MAX(INumber) FOR Issue in ([Issue1], [Issue2], [Issue3], [Issue4])) pv1
PIVOT  (MAX([Tonnage]) FOR TonnageHeader IN ([Tonnage1], [Tonnage2], [Tonnage3], [Tonnage4])) pv2
PIVOT  (Max([SubmittedDate]) FOR SubmittedDateHeader IN ([SubmittedDate1],[SubmittedDate2],[SubmittedDate3],[SubmittedDate4])) pv3
PIVOT  (Max([Revision]) FOR RevisionHeader IN ([Revision1], [Revision2], [Revision3], [Revision4])) pv4
GO

NNumber  | Issue1 | Issue2 | Issue3 | Issue4 | Tonnage1 | Tonnage2 | Tonnage3 | Tonnage4 | SubmittedDate1      | SubmittedDate2      | SubmittedDate3      | SubmittedDate4      | Revision1 | Revision2 | Revision3 | Revision4
:------- | -----: | -----: | -----: | -----: | -------: | -------: | -------: | -------: | :------------------ | :------------------ | :------------------ | :------------------ | :-------- | :-------- | :-------- | :--------
1        |      2 |   null |   null |   null |       12 |     null |     null |     null | 21/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
100      |      1 |   null |   null |   null |        6 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
11112    |      1 |   null |   null |   null |       71 |     null |     null |     null | null                | null                | null                | null                | 0         | null      | null      | null     
2        |      1 |   null |   null |   null |        6 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
23       |      1 |   null |   null |   null |       68 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
3        |      1 |   null |   null |   null |        4 |     null |     null |     null | null                | null                | null                | null                | 0         | null      | null      | null     
3333     |      2 |   null |   null |   null |       48 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 3         | null      | null      | null     
4        |      1 |   null |   null |   null |       45 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
555      |      1 |   null |   null |   null |        5 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
666666   |      1 |   null |   null |   null |       38 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
7        |      9 |   null |   null |   null |       32 |     null |     null |     null | null                | null                | null                | null                | 0         | null      | null      | null     
77       |      1 |   null |   null |   null |      487 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
88       |      1 |   null |   null |   null |        6 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
983      |      1 |   null |   null |   null |       76 |     null |     null |     null | null                | null                | null                | null                | 0         | null      | null      | null     
99       |      1 |   null |   null |   null |       91 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
D1       |      2 |   null |   null |   null |       42 |     null |     null |     null | null                | null                | null                | null                | 0         | null      | null      | null     
DT1      |   null |   null |   null |      2 |     null |     null |     null |       21 | null                | null                | null                | 18/04/2017 00:00:00 | null      | null      | null      | 1        
DT1      |   null |   null |      2 |   null |     null |     null |       24 |     null | null                | null                | 18/04/2017 00:00:00 | null                | null      | null      | 1         | null     
DT1      |   null |      3 |   null |   null |     null |       24 |     null |     null | null                | 18/04/2017 00:00:00 | null                | null                | null      | 1         | null      | null     
DT1      |      1 |   null |   null |   null |       24 |     null |     null |     null | null                | null                | null                | null                | 2         | null      | null      | null     
DTA05    |      1 |   null |   null |   null |       56 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
Dwg1     |   null |      1 |   null |   null |     null |       10 |     null |     null | null                | 18/04/2017 00:00:00 | null                | null                | null      | 0         | null      | null     
Dwg1     |      2 |   null |   null |   null |        4 |     null |     null |     null | 18/04/2017 00:00:00 | null                | null                | null                | 0         | null      | null      | null     
Dwg12345 |      1 |   null |   null |   null |       36 |     null |     null |     null | null                | null                | null                | null                | 0         | null      | null      | null

dbfiddle here

Update

Ok, if you want to UNION and GROUP all pivot results you can do it on this way:

NOTE: I've do it the work with the two first PIVOT results, because you must manually add all returned columns with null values to all the PIVOT queries.

 , DataSrc2 as
 (
     SELECT NNumber, INumber, Issue, Tonnage, TonnageHeader, SubmittedDate, SubmittedDateHeader,Revision, RevisionHeader
     FROM  DataSrc where NNumber = 'DT1'
 )
  , Pvt as
  (
      SELECT NNumber,SubmittedDate1,SubmittedDate2,SubmittedDate3,SubmittedDate4, null as Issue1, null as Issue2, null as Issue3, null Issue4
      from (select NNumber, SubmittedDateHeader, SubmittedDate from DataSrc2) src
      PIVOT  (Max([SubmittedDate]) FOR SubmittedDateHeader IN ([SubmittedDate1],[SubmittedDate2],[SubmittedDate3],[SubmittedDate4])) pv3
      UNION ALL
      SELECT NNumber, null as SubmittedDate1, null as SubmittedDate2, null as SubmittedDate3, null as SubmittedDate4, Issue1, Issue2, Issue3, Issue4  
      from (select NNumber, Issue, INumber from DataSrc2) src
      PIVOT  (MAX(INumber) FOR Issue in ([Issue1], [Issue2], [Issue3], [Issue4])) pv1
  )
  select NNumber, MAX(SubmittedDate1) SD1, MAX(SubmittedDate2) SD2, MAX(SubmittedDate3) SD3, MAX(SubmittedDate4) SD4,
                  MAX(Issue1) Iss1, MAX(Issue2) Iss2, MAX(Issue3) Iss3, MAX(Issue4) Iss4 
  from Pvt
  group by Nnumber;

And this is the result, but I'd recommend you to build a temporary table for this job.

NNumber | SD1  | SD2                 | SD3                 | SD4                 | Iss1 | Iss2 | Iss3 | Iss4
:------ | :--- | :------------------ | :------------------ | :------------------ | ---: | ---: | ---: | ---:
DT1     | null | 18/04/2017 00:00:00 | 18/04/2017 00:00:00 | 18/04/2017 00:00:00 |    1 |    3 |    2 |    2

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here

Thanks for the query, but it again gives me the same result, i need one record per Number if you see Number D1 which has 4 records i need all of them to be in a single row as D1 | 1 | 3 | 2 | 2 | Etc...................
Thanks @Andy i guess i am not explaining my requirement in clear way to you, i need one record per Number if a number contains more than one row then those rows to be combined together with removing null values
@VickySelvam if you want a spot on answer, you need to have a spot on explanation of what you have and what you need. In a day, we can multiply you by 10 000. 10 000 users who are not clear in their head on what they need ... That makes a lot of people. As I said in one of your previous question, MCNets or me are here to help but you have to know we all work pro bono, we are not paid for that and therefore you cannot use as a way to outsource your work or your lack of clarity.
@Andy thanks for the query i have made some changes to the query and it works fine as it need to be , anyway thank you so much for the help Andy
@VickySelvam I did nothing. It is McNets who did all the hard work, you should thanked him instead of me ;)

Andriy M · Accepted Answer · 2017-05-05 11:45:27Z

The following query uses the dwgIssue derived table from your query and pivots the results directly from it using conditional aggregation:

SELECT
  ID,
  Number,
  Issue1 = MAX(CASE rn WHEN 1 THEN [Issue Number] END),
  Issue2 = MAX(CASE rn WHEN 2 THEN [Issue Number] END),
  Issue3 = MAX(CASE rn WHEN 3 THEN [Issue Number] END),
  Issue4 = MAX(CASE rn WHEN 4 THEN [Issue Number] END),
  Tonnage1 = MAX(CASE rn WHEN 1 THEN Tonnage END),
  Tonnage2 = MAX(CASE rn WHEN 2 THEN Tonnage END),
  Tonnage3 = MAX(CASE rn WHEN 3 THEN Tonnage END),
  Tonnage4 = MAX(CASE rn WHEN 4 THEN Tonnage END),
  SubmittedDate1 = MAX(CASE rn WHEN 1 THEN SubmittedDate END),
  SubmittedDate2 = MAX(CASE rn WHEN 2 THEN SubmittedDate END),
  SubmittedDate3 = MAX(CASE rn WHEN 3 THEN SubmittedDate END),
  SubmittedDate4 = MAX(CASE rn WHEN 4 THEN SubmittedDate END),
  EORReturnDate1 = MAX(CASE rn WHEN 1 THEN EORReturnDate END),
  EORReturnDate2 = MAX(CASE rn WHEN 2 THEN EORReturnDate END),
  EORReturnDate3 = MAX(CASE rn WHEN 3 THEN EORReturnDate END),
  EORReturnDate4 = MAX(CASE rn WHEN 4 THEN EORReturnDate END),
  Revision1 = MAX(CASE rn WHEN 1 THEN Revision END),
  Revision2 = MAX(CASE rn WHEN 2 THEN Revision END),
  Revision3 = MAX(CASE rn WHEN 3 THEN Revision END),
  Revision4 = MAX(CASE rn WHEN 4 THEN Revision END)
FROM
  (
    SELECT
      dwgReg.ID,
      dwgIssue.[Issue Number],
      rn = ROW_NUMBER() OVER (PARTITION BY dwgIssue.[Drawing ID]
                              ORDER BY dwgReg.[ID], dwgRev.[Revision] DESC, dwgIssue.[Issue Number] DESC),
      dwgRev.Revision,
      Tonnage = isnull(dwgIssue.weight1,0)+isnull(dwgIssue.weight2,0)+isnull(dwgIssue.weight3,0),
      SubmittedDate = dwgRev.[SDate],
      EORReturnDate = dwgRev.[EDate],
      dwgReg.Number
    FROM
      dbo.Issue AS dwgIssue
      INNER JOIN Register AS dwgReg
              ON dwgIssue.[Drawing ID] = dwgReg.ID
      INNER JOIN Revision dwgRev
              ON dwgRev.ID=dwgIssue.[Revision ID]
                 AND dwgIssue.[Drawing ID]=dwgReg.ID                                                                 
    WHERE dwgReg.ProjectID=@ProjectID
  ) AS dwgIssue
WHERE
  rn <= 4
;

Conditional aggregation may have become less common as a pivoting method after the PIVOT operator was introduced. I, however, still find it preferable when I need to pivot more than one data column at once, like in your case.

Collectives™ on Stack Overflow

SQL Query with multiple PIVOT

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related