how to insert multiple row value in a single row

Question

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 21-Mar-13 23:51pm

Avinash_Pathak

Updated 22-Mar-13 0:16am

v2

Add a Solution

Comments

Maciej Los 22-Mar-13 6:02am

Not clear. Please, be more specific and provide more details (example data).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-03-22T00:18:00

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