Как группировать строки на основе значений столбцов

У меня есть данные в таблице, как показано ниже. Это всего лишь пример данных с только 1 AdmissionNo, реальные данные могут иметь сотни из них с их собственными наборами. Поэтому я хочу сгруппировать строки на основе AdmissionNo и WardCode, означает, что первые 13 строк с WardCode, поскольку «KINT» должны иметь значение grpValue как 1, затем следующие две строки как 2, затем 1 и так далее.

CREATE TABLE [dbo].[tbl_H](
    [AdmissionNo] [nvarchar](50) NULL,
    [WardCode] [nvarchar](255) NULL,
    [AdmissionDateTime] [datetime] NULL,
    [DischargeDateTime] [datetime] NULL,
    [date] [datetime] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-06-29 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-06-30 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-01 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-02 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-03 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-04 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-05 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-06 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-07 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-08 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-09 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-10 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KINT', CAST(N'2016-06-29 01:59:00.000' AS DateTime), CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-11 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-12 03:38:00.000' AS DateTime), CAST(N'2016-07-11 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-11 17:44:00.000' AS DateTime), CAST(N'2016-07-12 03:38:00.000' AS DateTime), CAST(N'2016-07-12 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KEHH', CAST(N'2016-07-12 03:38:00.000' AS DateTime), CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-07-12 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-12 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-13 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-14 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-15 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-16 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-17 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-19 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-20 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-21 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-22 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-23 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-24 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-25 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-26 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-27 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-28 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime], [date]) VALUES (N'0000001742', N'KCAR', CAST(N'2016-07-12 13:25:00.000' AS DateTime), CAST(N'2016-08-18 10:59:00.000' AS DateTime), CAST(N'2016-07-29 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tbl_H] ([AdmissionNo], [WardCode], [AdmissionDateTime], [DischargeDateTime
					

sql,sql-server,tsql,sql-server-2014,

0

Ответов: 2


1 принят

Я решил это с помощью следующего запроса, благодаря Аджай Гупте:

;WITH cte as (
SELECT *,
   ROW_NUMBER() OVER (PARTITION BY AdmissionNo,WardCode ORDER BY rownum) as rn,
   rownum - ROW_NUMBER() OVER (PARTITION BY AdmissionNo,WardCode ORDER BY rownum) as 
grp
FROM tbl_h
)    
SELECT *, DENSE_RANK() OVER (ORDER BY grp) as new_Grp
FROM cte
ORDER BY rownum

-1

Я думаю, это то, что вы хотите.

SELECT * 
FROM TABLE1 
GROUP BY AdmissinoNo,WardCode
ORDER BY AdmissinoNo,WardCode

Сначала Группируйте его по AdmissinoNo и WardCode, а затем сортируйте по AdmissinoNo и WardCode

SQL, SQL-сервер, TSQL, SQL-сервер 2014,
Похожие вопросы