使用多个列值的有效方法和别名
我有一些简单的键值对数据，用于跟踪用户和对“遭遇”进行特定编辑的日期时间，例如医生访问时他们会测量您的血压（谁和何时）和您的脉搏（谁和何时）以及您的体温（谁以及何时）。
以下是数据示例：
CREATE TABLE #temptable ( [encounterId] int, [rn] bigint, [eventDt] datetime2(3), [revisedBy] nvarchar(101), [TypeId] int )
INSERT INTO #temptable ([encounterId], [rn], [eventDt], [revisedBy], [TypeId])
VALUES
( 1, 1, N'2021-11-12T08:43:04', N'Samantha B', 4 ),
( 2, 1, N'2021-11-11T14:08:24', N'Testuser02 L', 1 ),
( 2, 1, N'2021-11-11T14:08:28', N'Testuser02 L', 2 ),
( 3, 1, N'2021-11-22T12:00:47', N'Johnny S', 1 ),
( 3, 2, N'2022-04-08T10:03:49', N'Johnny S', 1 ),
( 6, 1, N'2021-11-22T11:57:59', N'Johnny S', 1 ),
( 7, 1, N'2021-11-24T08:46:49', N'Johnny V', 1 ),
( 13, 1, N'2022-03-28T13:33:24', N'Johnny S', 4 ),
( 14, 1, N'2022-03-28T13:34:42', N'Johnny S', 4 )
SELECT * from #temptable
DROP TABLE #temptable
注释 TypeId值是已知值（例如1 =“Procedure Started”，2 =“Procedure Ended”等），有5个TypeId，并且TypeId每个encounterId可以发生多次，所以我添加了一个row_number（rn）每个encounterId、TypeId按eventDt顺序来标记这些实例。
我试图解决的问题
我需要将这些值转出以获取 meetId，但是我无法在我的应用程序中使用 PIVOT 或存储过程应用程序的技术（自定义报告工具）。我必须对结果进行 CASE 并以这种方式旋转值，以便每个遇到Id 都有一行显示每对 TypeId（何时）和 TypeIdRevisedBy（谁）。我担心 CASE 语句重复 15 次的效率（五个 TypeId 选项 x 每个 meetId 每个选项三个潜在用途）。
这是我的 CASE 语句的工作模型，但对列别名进行硬编码并使每个两层 CASE 语句每次都完整运行似乎很奇怪：
SELECT encounterId
, CASE WHEN TypeId=1 AND rn=1 THEN eventDt END AS [1Procedure Started]
, CASE WHEN TypeId=1 AND rn=1 THEN revisedBy END AS [1Procedure Started Revised By]
, CASE WHEN TypeId=1 AND rn=2 THEN eventDt END AS [2Procedure Started]
, CASE WHEN TypeId=1 AND rn=2 THEN revisedBy END AS [2Procedure Started Revised By]
, CASE WHEN TypeId=1 AND rn=3 THEN eventDt END AS [3Procedure Started]
, CASE WHEN TypeId=1 AND rn=3 THEN revisedBy END AS [3Procedure Started Revised By]
, CASE WHEN TypeId=2 AND rn=1 THEN eventDt END AS [1Procedure Ended]
, CASE WHEN TypeId=2 AND rn=1 THEN revisedBy END AS [1Procedure Ended Revised By]
, CASE WHEN TypeId=2 AND rn=2 THEN eventDt END AS [2Procedure Ended]
, CASE WHEN TypeId=2 AND rn=2 THEN revisedBy END AS [2Procedure Ended Revised By]
, CASE WHEN TypeId=2 AND rn=3 THEN eventDt END AS [3Procedure Ended]
, CASE WHEN TypeId=2 AND rn=3 THEN revisedBy END AS [3Procedure Ended Revised By]
--repeat loop through TypeId=5
FROM #temptable
问题
- 是否有一个对我来说，通过两项检查（TypeId 和 rn）进行 CASE 的更有效方法是什么？
- 是否有更有效的方法使用 rn 列的值来对 CASE 中的列进行别名（如我的硬编码别名中所示，但自动）？
I have some simple key-value pair data tracking the user and the datetime for when specific edits for an "encounter" were made, like a dr visit where they take your BP (who and when) and your pulse (who and when) and your temperature (who and when).
Here is a sample of the data:
CREATE TABLE #temptable ( [encounterId] int, [rn] bigint, [eventDt] datetime2(3), [revisedBy] nvarchar(101), [TypeId] int )
INSERT INTO #temptable ([encounterId], [rn], [eventDt], [revisedBy], [TypeId])
VALUES
( 1, 1, N'2021-11-12T08:43:04', N'Samantha B', 4 ),
( 2, 1, N'2021-11-11T14:08:24', N'Testuser02 L', 1 ),
( 2, 1, N'2021-11-11T14:08:28', N'Testuser02 L', 2 ),
( 3, 1, N'2021-11-22T12:00:47', N'Johnny S', 1 ),
( 3, 2, N'2022-04-08T10:03:49', N'Johnny S', 1 ),
( 6, 1, N'2021-11-22T11:57:59', N'Johnny S', 1 ),
( 7, 1, N'2021-11-24T08:46:49', N'Johnny V', 1 ),
( 13, 1, N'2022-03-28T13:33:24', N'Johnny S', 4 ),
( 14, 1, N'2022-03-28T13:34:42', N'Johnny S', 4 )
SELECT * from #temptable
DROP TABLE #temptable
Notes
The TypeId values are known values (e.g. 1="Procedure Started", 2="Procedure Ended", etc.), there are 5 TypeIds, and the TypeId can happen multiple times per encounterId, so I have added a row_number (rn) per encounterId, TypeId order by eventDt to mark these instances.
The problem I'm trying to solve
I need to pivot these values out for encounterId but I can't use PIVOT or Stored Procedures in my application's technology (custom reporting tool). I'm going to have to CASE through the results and pivot the values that way so that each encounterId has a single row showing each pair of TypeId (when) and TypeIdRevisedBy (who). I'm concerned about efficiency with a CASE statement repeating 15 times (five TypeId options x three potential uses each per encounterId).
Here is a working model of my CASE statements but it seems wonky to hard-code the column aliases and to have each two-layer CASE statement run in full each time:
SELECT encounterId
, CASE WHEN TypeId=1 AND rn=1 THEN eventDt END AS [1Procedure Started]
, CASE WHEN TypeId=1 AND rn=1 THEN revisedBy END AS [1Procedure Started Revised By]
, CASE WHEN TypeId=1 AND rn=2 THEN eventDt END AS [2Procedure Started]
, CASE WHEN TypeId=1 AND rn=2 THEN revisedBy END AS [2Procedure Started Revised By]
, CASE WHEN TypeId=1 AND rn=3 THEN eventDt END AS [3Procedure Started]
, CASE WHEN TypeId=1 AND rn=3 THEN revisedBy END AS [3Procedure Started Revised By]
, CASE WHEN TypeId=2 AND rn=1 THEN eventDt END AS [1Procedure Ended]
, CASE WHEN TypeId=2 AND rn=1 THEN revisedBy END AS [1Procedure Ended Revised By]
, CASE WHEN TypeId=2 AND rn=2 THEN eventDt END AS [2Procedure Ended]
, CASE WHEN TypeId=2 AND rn=2 THEN revisedBy END AS [2Procedure Ended Revised By]
, CASE WHEN TypeId=2 AND rn=3 THEN eventDt END AS [3Procedure Ended]
, CASE WHEN TypeId=2 AND rn=3 THEN revisedBy END AS [3Procedure Ended Revised By]
--repeat loop through TypeId=5
FROM #temptable
The Question(s)
- Is there a more efficient way for me to CASE through both checks (TypeId and rn)?
- Is there a more efficient way to alias the columns in the CASE using the rn column's value (as seen in my hard-coded aliases, but automagically)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论