I need help with creating a SQL Pivot and generating a unique ID column in the process, please.
I am working on SQL Server.
In the digram below I have displayed the source data. I need to pivot the data into another table, the data comes in pairs of 'Fail' and 'Pass' and I need to pivot the time at which they Failed and then passed.
I also need to have a unique ID field generated for each pivoted record - I have named this column GENERATE-ID in the diagram below.
I have created a SQL script for the test data:
CREATE TABLE [dbo].[TestDataForPivot](
[Location] [varchar](100) NULL,
[DateTimeStamp] [datetime] NULL,
[Result] [varchar](100) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A001', CAST(N'2023-03-01T10:00:00.000' AS DateTime), N'Fail')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A001', CAST(N'2023-03-01T10:10:00.000' AS DateTime), N'Pass')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A002', CAST(N'2023-03-01T14:00:00.000' AS DateTime), N'Fail')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A002', CAST(N'2023-03-01T14:20:00.000' AS DateTime), N'Pass')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A002', CAST(N'2023-03-01T14:30:00.000' AS DateTime), N'Fail')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A002', CAST(N'2023-03-01T15:30:00.000' AS DateTime), N'Pass')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A003', CAST(N'2023-03-01T20:30:00.000' AS DateTime), N'Fail')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A003', CAST(N'2023-03-01T20:35:00.000' AS DateTime), N'Pass')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A001', CAST(N'2023-03-01T20:30:00.000' AS DateTime), N'Fail')
GO
INSERT [dbo].[TestDataForPivot] ([Location], [DateTimeStamp], [Result]) VALUES (N'A001', CAST(N'2023-03-02T11:10:00.000' AS DateTime), N'Pass')
GO
I have tried to do a pivot myself but I am not being able to get the result. I am not sure how to generate the ID column and also with the pivot only one row is being generated where LOCATION = A002 - because ont he pivot I am having to use an agregate function of MIN or MAX
I hope you can help me out.
If everything is correct, it will work. If the other state is different from your output, state it so that the code is complete
select 1000 +ROW_NUMBER() over(order by (select 0))as Genetate_ID,
[Location],[DateTimeStamp] as FAil,
lgDateTimeStamp as PAss
from (
select *,lead([DateTimeStamp]) over(partition by [Location] order by [DateTimeStamp]) as lgDateTimeStamp
,lead([Result]) over(partition by [Location] order by [DateTimeStamp]) as lgResult
from [TestDataForPivot]
)a
where lgResult='Pass' and Result='Fail'
order by [DateTimeStamp]
Result:
| Genetate_ID | Location | FAil | PAss |
|---|---|---|---|
| 1001 | A001 | 2023-03-01 10:00:00.000 | 2023-03-01 10:10:00.000 |
| 1002 | A002 | 2023-03-01 14:00:00.000 | 2023-03-01 14:20:00.000 |
| 1003 | A002 | 2023-03-01 14:30:00.000 | 2023-03-01 15:30:00.000 |
| 1004 | A003 | 2023-03-01 20:30:00.000 | 2023-03-01 20:35:00.000 |
| 1005 | A001 | 2023-03-01 20:30:00.000 | 2023-03-02 11:10:00.000 |