Vicky S
Reputation: 832
SQL Pivot and Function
USE [WPF]
GO
/****** Object: Table [dbo].[Issue] Script Date: 17/04/2017 4:45:16 PM ******/
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,
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
/****** Object: Table [dbo].[Register] Script Date: 17/04/2017 4:45:16 PM ******/
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
/****** Object: Table [dbo].[Revision] Script Date: 17/04/2017 4:45:16 PM ******/
CREATE TABLE [dbo].[Revision](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NULL,
[Drawing ID] [int] NULL,
[Revision] [nvarchar](255) 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]) VALUES (9, 23, 3, 5, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (10, 23, 4, 6, 9)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (19, 23, 7, 12, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (164, 23, 12, 61, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (165, 23, 13, 62, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (167, 23, 13, 62, 0)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (168, 23, 13, 62, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (169, 23, 13, 64, 0)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (170, 23, 13, 64, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (171, 23, 13, 64, 3)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (254, 23, 5, 86, 4)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (256, 23, 13, 88, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (257, 23, 13, 89, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (258, 23, 13, 64, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (259, 23, 16, 91, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (260, 23, 16, 91, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (297, 2, 17, 108, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (298, 2, 17, 108, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (299, 2, 17, 108, 6)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (300, 2, 17, 109, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (301, 2, 17, 109, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (302, 2, 17, 109, 3)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (303, 2, 17, 110, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (304, 2, 17, 110, 2)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (305, 2, 17, 110, 7)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (306, 2, 17, 110, 8)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (307, 2, 17, 110, 6)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (308, 23, 18, 111, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (309, 23, 19, 112, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (310, 23, 20, 113, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (311, 23, 21, 114, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (312, 23, 22, 115, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (313, 23, 23, 116, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (314, 23, 24, 117, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (315, 23, 25, 118, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (316, 23, 26, 119, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (317, 23, 27, 120, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (318, 23, 28, 121, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (319, 23, 29, 122, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (320, 23, 30, 123, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (321, 26, 31, 124, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (322, 26, 32, 125, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (323, 26, 33, 126, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (324, 26, 34, 127, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (325, 26, 35, 128, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (326, 2, 36, 129, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (327, 26, 37, 130, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (328, 2, 38, 131, 1)
GO
INSERT [dbo].[Issue] ([ID], [ProjectID], [Drawing ID], [Revision ID], [Issue Number]) VALUES (329, 23, 39, 132, 1)
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]) VALUES (5, 23, 3, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (6, 23, 4, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (12, 23, 7, N'3')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (61, 23, 12, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (62, 23, 13, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (64, 23, 13, N'1')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (88, 23, 13, N'1')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (89, 23, 13, N'2')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (90, 23, 13, N'1')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (91, 23, 16, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (108, 2, 17, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (109, 2, 17, N'1')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (110, 2, 17, N'2')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (111, 23, 18, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (112, 23, 19, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (113, 23, 20, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (114, 23, 21, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (115, 23, 22, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (116, 23, 23, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (117, 23, 24, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (118, 23, 25, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (119, 23, 26, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (120, 23, 27, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (121, 23, 28, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (122, 23, 29, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (123, 23, 30, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (124, 26, 31, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (125, 26, 32, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (126, 26, 33, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (127, 26, 34, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (128, 26, 35, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (129, 2, 36, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (130, 26, 37, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (131, 2, 38, N'0')
GO
INSERT [dbo].[Revision] ([ID], [ProjectID], [Drawing ID], [Revision]) VALUES (132, 23, 39, N'0')
GO
SET IDENTITY_INSERT [dbo].[Revision] OFF
GO
I have 3 tables as Register, Revision, Issue i need latest 4 issues ordered by desc of Issue ID, Revision ID i have successfully made a query as follows,
SELECT
[DrawingID],
[RevisionID],
[IssueID],
Number,
[Revision],
[Issue Number],
'Issue' + Cast(rn AS CHAR(10)) AS Issue
FROM (
SELECT dwgReg.Number,
dwgRev.Revision,
dwgIssue.[Issue Number],
dwgReg.ID as DrawingID,
dwgRev.ID as RevisionID,
dwgIssue.ID as IssueID,
Row_number()
OVER (
partition BY dwgIssue.[Drawing ID]
ORDER BY dwgReg.[ID], dwgRev.[Revision]
DESC, dwgIssue.[Issue Number] DESC)
AS rn
FROM Issue dwgIssue
INNER JOIN Register dwgReg
ON dwgIssue.[Drawing ID]=dwgReg.ID
INNER JOIN Revision dwgRev
ON dwgRev.ID=dwgIssue.[Revision ID]
AND dwgRev.[Drawing ID]=dwgReg.ID)dwgIssue
where rn<=4
i just need to pivot this query with the help of SQL functions and the result needs to be like,
DrawingID Number Issue1 Issue2 Issue3 Issue4
each and every drawing will have maximum of 4 rows and minimum of 1 row. please some one help me out with a query.
Upvotes: 0
Views: 78
Answers (2)
Sachin Dubey
Reputation: 39
USE TestDB GO
WITH CTE
AS ( SELECT [Drawing ID] ,
MAX(CASE WHEN IssueCount = 1 THEN ID
END) AS Issue_1 ,
MAX(CASE WHEN IssueCount = 2 THEN ID
END) AS Issue_2 ,
MAX(CASE WHEN IssueCount = 3 THEN ID
END) AS issue_3 ,
MAX(CASE WHEN IssueCount = 4 THEN ID
END) AS issue_4
FROM ( SELECT ID ,
ProjectID ,
[Drawing ID] ,
[Revision ID] ,
[Issue Number] ,
ROW_NUMBER() OVER ( PARTITION BY [Drawing ID] ORDER BY [Revision ID] DESC ) AS IssueCount
FROM dbo.ISSUE (NOLOCK)
) i
GROUP BY [Drawing ID]
)
SELECT [Drawing ID] ,
Issue_1 ,
Issue_2 ,
issue_3 ,
issue_4
FROM CTE
Upvotes: 1
Gordon Linoff
Reputation: 1269973
I would just use conditional aggregation. Here is what to do for the issues:
select i.drawingid,
max(case when seqnum = 1 then id end) as issueid_1,
max(case when seqnum = 2 then id end) as issueid_2,
max(case when seqnum = 3 then id end) as issueid_3,
max(case when seqnum = 4 then id end) as issueid_4
from (select i.*,
row_number() over (partition by i.drawingid order by i.revisionid desc) as seqnum
from issue i
) i
group by i.drawingid;
The rest is just joining in the additional columns that you want.
Upvotes: 2