Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MySQL
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 22-Mar-13 0:51am
Edited 22-Mar-13 1:16am
v2
Comments
Maciej Los at 22-Mar-13 6:02am
   
Not clear. Please, be more specific and provide more details (example data).

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

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



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100