Query Needed

Question

Post reply

Query Needed

vs.satheesh

SSCrazy

Points: 2392
More actions
February 2, 2016 at 10:37 pm

#315613

Dear All
In my scenario i have Patient Appointment details table.In this table i have three column
Sno
PatientID
Appoiintment_Date
TableInfo
Sno PatID Appoiintment_Date
1 , P1 , 01-01-2016
2 , P1 , 02-01-2016
3 , P1 , 03-01-2016
4 , P1 , 04-01-2016
5 , P2 , 01-01-2016
6 , P2 , 02-01-2016
7 , P3 , 01-01-2016
8 , P3 , 02-01-2016
9 , P4 , 03-01-2016
10 , P5 , 01-01-2016
In this above table i have five patient appointment details. Each patient applying multiple appointments.Now i want following structure result,
PatID Date1 date2 Date3
P1 , 01-01-2016 , 02-01-2016, 03-01-2016
P1 , 04-01-2016 , null , null
P2 , 01-01-2016 , 02-01-2016 , null
P3 , 01-01-2016 , 02-01-2016 , null
P4 , 03-01-2016 , null , null
P5 , 01-01-2016 , null , null
Here my result set Date1,date2 and date3 is static.Any patient apply more then 3 three time the result should come in next line(example P1).Please help me Query.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ROW_NUMBER() tricks with a CROSSTAB:

CREATE TABLE #PatientAppointmentDetails (Sno INT, PatID CHAR(2), Appoiintment_Date DATETIME)

INSERT INTO #PatientAppointmentDetails (Sno, PatID, Appoiintment_Date) VALUES

(1 , 'P1' , '01-01-2016'),

(2 , 'P1' , '02-01-2016'),

(3 , 'P1' , '03-01-2016'),

(4 , 'P1' , '04-01-2016'),

(5 , 'P2' , '01-01-2016'),

(6 , 'P2' , '02-01-2016'),

(7 , 'P3' , '01-01-2016'),

(8 , 'P3' , '02-01-2016'),

(9 , 'P4' , '03-01-2016'),

(10 , 'P5' , '01-01-2016');

WITH ExtraGroup AS (

SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY PatID ORDER BY Appoiintment_Date) - 1

FROM #PatientAppointmentDetails

)

SELECT

PatID,

Date1 = MAX(CASE WHEN rn%3 = 0 THEN Appoiintment_Date END),

date2 = MAX(CASE WHEN rn%3 = 1 THEN Appoiintment_Date END),

Date3 = MAX(CASE WHEN rn%3 = 2 THEN Appoiintment_Date END)

FROM ExtraGroup

GROUP BY PatID, rn/3

ORDER BY PatID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

Looks like a formatting problem to me. Formatting should be handled on the client side, not by the database.

If you want to disregard that advice, then you'll need a "crosstab" or "pivot" query. The basic pattern for this is:

SELECT KeyColumn,

MAX(CASE WHEN (this row belongs in first column) THEN DataValue END) AS FirstColumn,

MAX(CASE WHEN (this row belongs in second column) THEN DataValue END) AS SecondColumn,

...

FROM YourTable

GROUP BY KeyColumn;

For the condition in your problem, look at the ROW_NUMBER() function and at division remainder.

I cannot gove you more code than this because you didn't give me CREATE TABLE and INSERT statements to build a query on. But if you piece together the components you should be able to get a solution, and learn more than you would from using copy/paste.

EDIT: Or you can copy/paste the solution that Chris posted while I was typing this. But make sure to understand all the components used, or you'll have a bad time when you need to do maintenance.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TEJABI Old Hand Points: 368 More actions · Answer 3

sample logic but super :). i am trying about to 2hrs but i am not getting good result.thanks ChrisM@Work