SQL Server SQL Pivot and Partition And Generate ID

5ssjco0h 于 2023-05-05 发布在其他

关注(0)|答案(2)|浏览(103)

I need help with creating a SQL Pivot and generating a uniquue 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.

Figure 1.

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.

sql-server

来源：https://stackoverflow.com/questions/76149282/sql-pivot-and-partition-and-generate-id

2条答案

按热度按时间

3zwjbxry1#

Here's one way, possibly:

SELECT [GENERATE-ID] = 1000 + ROW_NUMBER() OVER (ORDER BY Fail),
  Location, Fail, Pass
FROM 
(
  SELECT Location, 
    Fail =      CASE Result WHEN 'Fail' THEN DateTimeStamp END,
    Pass = LEAD(CASE Result WHEN 'Pass' THEN DateTimeStamp END, 1) 
           OVER (PARTITION BY Location ORDER BY DateTimeStamp)
  FROM dbo.TestDataForPivot
) AS x
WHERE Fail IS NOT NULL
ORDER BY Fail;

Example db<>fiddle

Based on comments, this will handle the case where a fail is missing a pass, but not the opposite.

赞(0）回复(0）举报 2023-05-05

r55awzrz2#

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

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server SQL Pivot and Partition And Generate ID

2条答案

相关问题

热门标签

最新问答