All geek questions in one place

SQL query with multiple PIVOT

I have 3 tables like Register, Revision, Issue. table structure and some data are

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 that will return one row for one drawing with the last 4 problems, which are grouped by the description of the RevID (Review Table), and then by the ISsue ID (Problem Table), he needs to select the 4 best entries for each drawing and all four entries should be combined with the swivel as if

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

I tried something like this:

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 fail in a few pivot so please help me solve this problem.

Thanks at Advance.

+4
source share
2 answers

dwgIssue, , :

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
;

, PIVOT. , , , , .

+2

?

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

, , UNION GROUP :

. PIVOT, PIVOT.

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

, .

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

+4

Source: https://habr.com/ru/post/1675596/

More articles:


All Articles