22,207
社区成员
发帖
与我相关
我的任务
分享求助高手，以下如何用编程实现？
表一“aa”如下：
如何分类将内容根据index插入到表二 "bb"中，表二如下：
以上只是举个例子具体有几万行，请哪位高手帮忙一下不胜感谢！！！
如何分类将内容根据index插入到表二 "bb"中，表二如下：
以上只是举个例子具体有几万行，请哪位高手帮忙一下不胜感谢！！！
...全文
请发表友善的回复…
发表回复
啰哩 2019-07-22
- 打赏
- 举报
TO 听雨停了
您好！请教一下，以上表格原始数据如果每天都会更新，怎么让它自动运行，保持我需要的表格处于最新状态？
- 就是行转列后按日期定时插入一行行数据
啰哩 2019-07-19
- 打赏
- 举报
好的，有问题再向高手请教
啰哩 2019-07-19
- 打赏
- 举报
好的，有问题再向高手请教
听雨停了 2019-07-18
- 打赏
- 举报
动态行转列了解一下
啰哩 2019-07-18
- 打赏
- 举报
好，以上是不是主要从select convert开始？如果date很多的话，是不是要写很多条select top？
吉普赛的歌 2019-07-18
- 打赏
- 举报
USE tempdb
GO
IF OBJECT_ID('dbo.[aa]') IS NOT NULL
DROP TABLE dbo.[aa]
GO
CREATE TABLE dbo.[aa](
[id] INT
,[date] datetime
,[index] NVARCHAR(10)
,[value] DECIMAL(10,2)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[aa] VALUES(N'1',N'2019/7/13 05:29:59',N'a1',N'4.3')
INSERT INTO dbo.[aa] VALUES(N'2',N'2019/7/13 05:29:59',N'b1',N'33')
INSERT INTO dbo.[aa] VALUES(N'3',N'2019/7/13 05:29:59',N'c1',N'2.9')
INSERT INTO dbo.[aa] VALUES(N'4',N'2019/7/13 05:29:59',N'd1',N'3.44')
INSERT INTO dbo.[aa] VALUES(N'5',N'2019/7/13 05:29:59',N'e1',N'3.9')
INSERT INTO dbo.[aa] VALUES(N'6',N'2019/7/14 05:29:59',N'a1',N'555')
INSERT INTO dbo.[aa] VALUES(N'7',N'2019/7/14 05:29:59',N'b1',N'43')
INSERT INTO dbo.[aa] VALUES(N'8',N'2019/7/14 05:29:59',N'c1',N'23')
INSERT INTO dbo.[aa] VALUES(N'9',N'2019/7/14 05:29:59',N'd1',N'3.9')
INSERT INTO dbo.[aa] VALUES(N'10',N'2019/7/14 05:29:59',N'e1',N'4.98')
INSERT INTO dbo.[aa] VALUES(N'11',N'2019/7/15 05:29:59',N'a1',N'3.78')
INSERT INTO dbo.[aa] VALUES(N'12',N'2019/7/15 05:29:59',N'b1',N'44')
INSERT INTO dbo.[aa] VALUES(N'13',N'2019/7/15 05:29:59',N'c1',N'32')
INSERT INTO dbo.[aa] VALUES(N'14',N'2019/7/15 05:29:59',N'd1',N'456')
INSERT INTO dbo.[aa] VALUES(N'15',N'2019/7/15 05:29:59',N'e1',N'33.9')
INSERT INTO dbo.[aa] VALUES(N'16',N'2019/7/16 05:29:59',N'a1',N'3.4')
INSERT INTO dbo.[aa] VALUES(N'17',N'2019/7/16 05:29:59',N'b1',N'3.3')
INSERT INTO dbo.[aa] VALUES(N'18',N'2019/7/16 05:29:59',N'c1',N'4.5')
INSERT INTO dbo.[aa] VALUES(N'19',N'2019/7/16 05:29:59',N'd1',N'5.4')
INSERT INTO dbo.[aa] VALUES(N'20',N'2019/7/16 05:29:59',N'e1',N'2.9')
SELECT CONVERT(CHAR(19),[date],120) AS [date]
,(SELECT TOP 1 a1.[value] FROM dbo.aa AS a1 WHERE a.[date]=a1.[date] AND a1.[index]='a1' ) AS a1
,(SELECT TOP 1 a1.[value] FROM dbo.aa AS a1 WHERE a.[date]=a1.[date] AND a1.[index]='b1' ) AS b1
,(SELECT TOP 1 a1.[value] FROM dbo.aa AS a1 WHERE a.[date]=a1.[date] AND a1.[index]='c1' ) AS c1
,(SELECT TOP 1 a1.[value] FROM dbo.aa AS a1 WHERE a.[date]=a1.[date] AND a1.[index]='d1' ) AS d1
,(SELECT TOP 1 a1.[value] FROM dbo.aa AS a1 WHERE a.[date]=a1.[date] AND a1.[index]='e1' ) AS e1
FROM aa AS a GROUP BY [date]
