如何在一行中插入多个行值 [英] how to insert multiple row value in a single row
问题描述
大家好,
在我的mysql表中有4个字段。 i> id(主要)ii> patientid iii> testid iv> date。
假设表中有三次patientid 01,但是2个记录的日期相同,剩下的1个是在不同的日期。我想选择相同的日期和相同的patientid记录作为1记录,他们的testid也应该在一行中。在这种情况下,它应该在一列中显示2个测试ID。
示例::
Id PID TestId日期
1 22 17 22/03/2013
2 22 18 22 / 03.2013
3 22 19 25/03/2013
4 21 17 22/03/2013
输出
Id PID TestId日期
1 22 17 18 22/03/2013
2 22 19 25/03/2013
3 21 17 22/03/2013
请帮忙,我卡住了。在此先感谢
如何:将行合并为单行?
示例:
- 第1步 - 创建临时表 -
IF NOT OBJECT_ID (N ' #BooksGenre') IS NULL
DROP 表# BooksGenre
CREATE TABLE #BooksGenre(
BookId INT NOT NULL ,
AuthorID INT NOT NULL ,
类型 NVARCHAR ( 50 ))
- 第2步 - 插入临时数据 -
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 1 , 1 ,' 喜剧')
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 1 , 1 ,' 幻想)
INSERT INTO #BooksGenre([BookId], [AuthorId],[类型])
VALUES ( 1 , 1 ,' 短篇小说')
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 2 , 1 ,' travel')
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 2 , 1 ,' guide' )
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 303 , 55 ,' 科幻小说')
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 303 , 55 ,' 恐怖')
INSERT INTO #BooksGenre([BookId],[AuthorId] ],[类型])
VALUES ( 1015 , 99 ,' 悲剧')
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 1015 , 99 ,' novel')
INSERT INTO #BooksGenre([BookId],[AuthorId],[Genre])
VALUES ( 1015 , 99 ,' drama')
- 第3步 - 解决方案A - 使用数据透视
DECLARE @cols NVARCHAR ( 200 )
DECLARE @ subqry < span class =code-keyword> NVARCHAR ( 400 )
DECLARE @ pivqry NVARCHAR ( 1000 )
- 动态列,类型列表
SET @cols = STUFF(( SELECT DISTINCT ' ],[' + [Genre]
FROM [#BooksGenre]
ORDER BY ' ],[' + [Genre]
FOR XML PATH(' ')), 1 , 2 ,' ' )+ ' ]'
- SELECT @cols AS [Genre]
SET @subqry = N ' SELECT [AuthorId],[BookId],[Genre]' +
' FROM [#BooksGenre]'
- EXEC(@subqry)
SET @pivqry = N ' SELECT PT。[AuthorId],[BookId],' + @cols + ' ' +
FROM(' + @ subqry + ' )AS DT' +
' PIVOT(COUNT(DT。[Genre] )FOR DT。[类型] IN(' + @cols + ' ))AS PT' +
' ORDER BY PT。 [AuthorId],[BookId]'
EXEC ( @ pivqry )
- 第4步 - 解决方案B - 使用STUFF ... FOR XML PATH
SELECT t1。[AuthorId],t1。[BookId],STUFF(( SELECT ' ,' + [Genre] AS ' text()'
FROM #BooksGenre AS t2
WHERE t2.BookId = t1.BookId
FOR XML PATH(' ')), 1 , 1 ,' ') AS [关键字]
FROM #BooksGenre < span class =code-keyword> AS t1
GROUP BY t1 。[AuthorId],t1。[BookId]
- 第5步 - 清理 -
DROP 表 #BooksGenre
Hi to all,
In my mysql table there are 4 fields. i>id(primary) ii>patientid iii>testid iv>date.
Suppose there is a patientid 01 three times in a table but 2 records are of same date and remaining 1 is on different date. I want to select same date and same patientid record as 1 record and their testid also should come in a single row. In this case it should display 2 test id in a single column.
Example::
Id PID TestId Date
1 22 17 22/03/2013
2 22 18 22/03.2013
3 22 19 25/03/2013
4 21 17 22/03/2013
output
Id PID TestId Date
1 22 17 18 22/03/2013
2 22 19 25/03/2013
3 21 17 22/03/2013
Please help , I have stuck. Thanks in advance
How to: merge rows into single row?
Example:
-- STEP 1 - CREATE TEMPORARY TABLE -- IF NOT OBJECT_ID(N'#BooksGenre') IS NULL DROP TABLE #BooksGenre CREATE TABLE #BooksGenre ( BookId INT NOT NULL, AuthorID INT NOT NULL, Genre NVARCHAR (50)) -- STEP 2 - INSERT TEMPORARY DATA -- INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (1, 1, 'comedy') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (1, 1, 'fantasy') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (1, 1, 'short story') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (2, 1, 'traveling') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (2, 1, 'guide') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (303, 55, 'science fiction') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (303, 55, 'horror') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (1015, 99, 'tragedy') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (1015, 99, 'novel') INSERT INTO #BooksGenre ([BookId], [AuthorId], [Genre]) VALUES (1015, 99, 'drama') -- STEP 3 - SOLUTION A -- using pivot DECLARE @cols NVARCHAR(200) DECLARE @subqry NVARCHAR(400) DECLARE @pivqry NVARCHAR(1000) --dynamic columns, list of genre SET @cols = STUFF((SELECT DISTINCT '],[' + [Genre] FROM [#BooksGenre] ORDER BY '],[' + [Genre] FOR XML PATH('')),1,2,'') + ']' --SELECT @cols AS [Genre] SET @subqry = N'SELECT [AuthorId],[BookId],[Genre] ' + 'FROM [#BooksGenre]' --EXEC (@subqry) SET @pivqry= N'SELECT PT.[AuthorId], [BookId], ' + @cols + ' ' + 'FROM (' + @subqry + ') AS DT ' + 'PIVOT (COUNT(DT.[Genre]) FOR DT.[Genre] IN (' + @cols + ')) AS PT ' + 'ORDER BY PT.[AuthorId], [BookId]' EXEC (@pivqry) -- STEP 4 - SOLUTION B -- using STUFF ... FOR XML PATH SELECT t1.[AuthorId], t1.[BookId], STUFF( (SELECT ', ' + [Genre] AS 'text()' FROM #BooksGenre AS t2 WHERE t2.BookId = t1.BookId FOR XML PATH('')), 1, 1, '') AS [Keywords] FROM #BooksGenre AS t1 GROUP BY t1.[AuthorId], t1.[BookId] -- STEP 5 - CLEAN UP -- DROP TABLE #BooksGenre
这篇关于如何在一行中插入多个行值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!