sqlserver数据库将多行数据按照一定规则进行合并为一条数据

            ## sqlserver数据库将多行数据按照一定规则进行合并为一条数据

结果演示

处理方案

通过存储过程将数据进行区分再组合最总实现分组 sql如下
创建测试表及数据

CREATE TABLE [dbo].[Table_1](
 [time] [datetime] NULL,
 [id] [varchar](50) NULL,
 [nursing] [varchar](50) NULL,
 [fz] [varchar](50) NULL,
 [xh] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (CAST(0x0000AA2900DE32D0 AS DateTime), N'1', N'11111111', N'┑', N'1')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'1', N'233232323', N'┃', N'2')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'1', N'2wewweee', N'┙', N'3')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (CAST(0x0000AA2900DF05C0 AS DateTime), N'1', N'2222212121', N'┑', N'32')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'1', N'2aqwqsww', N'┙', N'33')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (CAST(0x0000AA2900E17E90 AS DateTime), N'3', N'frfrfrfrfrfrfrffff', N'', N'42')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (CAST(0x0000AA2900E17E90 AS DateTime), N'4', N' fgfgfgfgfgf', N'┑', N'44')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'4', N'rtrtrtrtr', N'┙', N'45')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (CAST(0x0000AA2900E5E390 AS DateTime), N'5', N'4t4t4t4t4t', N'┑', N'55')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'5', N'hththththt', N'┙', N'56')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (CAST(0x0000AA2C00A7D0F0 AS DateTime), N'1', N'wdsdssd', N'┑', N'57')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'1', N'wdwddwdwd', N'┃', N'58')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'1', N'efefefefefef', N'┃', N'59')
INSERT [dbo].[Table_1] ([time], [id], [nursing], [fz], [xh]) VALUES (NULL, N'1', N'ddfdffdfdfdf', N'┙', N'60')

创建结果存储表结构

CREATE TABLE [dbo].[Table_2](
 [time] [datetime] NULL,
 [id] [varchar](50) NULL,
 [nursing] [varchar](50) NULL
) ON [PRIMARY]

处理数据的存储过程

ALTER PROCEDURE [dbo].[NurseRecordInhos1] 
AS
BEGIN
 CREATE TABLE [dbo].[#NurseRecord1](
 [time] [datetime] NULL,
 [id] [varchar](50) NULL,
 [nursing] [varchar](50) NULL,
 [fz] [varchar](50) NULL,
 [xh] [varchar](50) NULL,
 [dd] [int] NULL
 )
 CREATE TABLE [dbo].[#NurseRecord2] (
 [time] [datetime] NULL,
 [id] [varchar](50) NULL,
 [nursing] [varchar](50) NULL,
 [fz] [varchar](50) NULL,
 [xh] [varchar](50) NULL,
 [tt] [int] NULL
 )
 CREATE TABLE [dbo].[#NurseRecord3] (
 [time] [datetime] NULL,
 [id] [varchar](50) NULL,
 [nursing] [varchar](50) NULL
 )
 insert into #NurseRecord1 (time,id,nursing,fz,xh,dd) 
 (select a.*,ROW_NUMBER()over(order by time) as dd from  lianxi.dbo.Table_1 a where (fz is not null and fz='┑') and time is not null)  --抓取NSIS2071.dbo.NurseItemRecord 中
    insert into #NurseRecord2 (time,id,nursing,fz,xh,tt) 
    (select b.*,ROW_NUMBER()over(order by time) as tt from  lianxi.dbo.Table_1 b where (fz ='┙' and fz<>'')) 
    DECLARE my_cursor cursor
  FOR  (SELECT dd FROM #NurseRecord1)--抓取  放入游标 my_cursor 内
  OPEN my_cursor  --打开游标
  declare @ddd int
  --insert into Table_4   select * from  lianxi.dbo.Table_1
  --delete Table_4  select  * from table_4
  FETCH NEXT FROM my_cursor INTO @ddd --取出游标中的一条记录装入变量
  WHILE @@FETCH_STATUS=0
     BEGIN
     --declare @ss varchar(20)
     declare @nu varchar(20)
     select 'zzzz0' 
     select @ddd
       insert into #NurseRecord3 (time, id,nursing) select  sss.da,sss.db,sss.yyy from  ( 
   select    (select time from #NurseRecord1 where dd=@ddd) da, (select id from #NurseRecord1 where dd=@ddd) db,((select nursing from lianxi.dbo.Table_1 where xh= (select xh from #NurseRecord1 where dd=@ddd)) +isnull(ss.aaa,'')+ (select nursing from lianxi.dbo.Table_1 where xh=  (select xh from #NurseRecord2 where tt=@ddd))) yyy  from (select aaa=stuff   ((select b.nursing+'' from lianxi.dbo.Table_1 b where b.id = (select id from #NurseRecord1 where dd=@ddd) and xh>(select xh from #NurseRecord1 where dd=@ddd) and xh<(select xh from #NurseRecord2 where tt=@ddd)  for xml path('')),1,0,'') )ss) sss 
 FETCH NEXT FROM my_cursor INTO @ddd --取出下一行游标中的一条记录装入变量
     END
     close my_cursor
  DEALLOCATE my_cursor
   BEGIN
   select '1mn'
   select * from  #NurseRecord3
   delete lianxi.dbo.Table_2  
   INSERT INTO  lianxi.dbo.Table_2   select * from #NurseRecord3
   insert into lianxi.dbo.Table_2    select a.time,a.id,a.nursing from lianxi.dbo.Table_1 a where a.fz='' 
   END   
   TRUNCATE TABLE #NurseRecord3
   TRUNCATE TABLE #NurseRecord2
   TRUNCATE TABLE #NurseRecord1
 END

以上就是全部的内容！！