--创建测试表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows]') AND type in (N'U'))
DROP TABLE [dbo].[TestRows]
GO
CREATE TABLE [dbo].[TestRows](
[Id] [int] IDENTITY(1,1) NOT NULL,
[MachineId] [nvarchar](50) NULL,
[SpareName] [nvarchar](50) NULL,
[SpareModel] [nvarchar](50) NULL,
[EquipmentLeadDate] [datetime] NULL,
[DataStorageTime] [datetime] NULL,
[CumulativeServiceLife] [numeric](18, 0) NULL,
) ON [PRIMARY]
GO
--插入测试数据
INSERT INTO [TestRows] ([MachineId],[SpareName],[SpareModel],[EquipmentLeadDate],[DataStorageTime],[CumulativeServiceLife])
SELECT N'A',N'Z-1',N'A1',N'2017-09-11',N'2017-10-11',60 UNION ALL
SELECT N'A',N'Z-1',N'A1',N'2017-09-12',N'2017-10-12',70 UNION ALL
SELECT N'B',N'S-1',N'B1',N'2017-09-11',N'2017-10-11',80 UNION ALL
SELECT N'B',N'S-1',N'B1',N'2017-09-14',N'2017-10-14',75 UNION ALL
SELECT N'B',N'S-3',N'B2',N'2017-09-15',N'2017-10-15',57 UNION ALL
SELECT N'C',N'C-3',N'C1',N'2017-09-13',N'2017-10-13',80 UNION ALL
SELECT N'A',N'Z-1',N'Z1',N'2017-09-20',N'2017-10-20',100
GO
SELECT * FROM [TestRows]
若[MachineId],[SpareName],[SpareModel] 3列组合为相同进行合并为一行数据,列累加展示,列个数是动态变化的。
结果为:
MachineId SpareName SpareModel EquipmentLeadDate DataStorageTime CumulativeServiceLife EquipmentLeadDate DataStorageTime CumulativeServiceLife sumCumulativeServiceLife
A Z-1 A1 2017-9-11 2017-10-11 60 2017-9-12 2017-10-12 70 130
B S-1 B1 2017-9-11 2017-10-11 75 2017-9-14 2017-10-14 80 155
B S-3 B2 2017-9-15 2017-10-15 57 57
C C-3 C1 2017-9-13 2017-10-13 80 80
A Z-1 Z1 2017-9-20 2017-10-20 100 100