Click here to Skip to main content
15,030,781 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 22-Mar-13 0:16am
v2
Comments
Maciej Los 22-Mar-13 6:02am
   
Not clear. Please, be more specific and provide more details (example data).

1 solution

How to: merge rows into single row?

Example:
SQL
-- 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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900