March 26, 2010 at 3:12 am
I have two tables with a one-to-many relationship: Patient and Followups. I would like a SELECT to show the selected patients one for each row, and the followups belonging to that patient on the same row in one column for each followup. I think it's called cross-tab(?). I want the result to look like this:
[font="Courier New"]patientIDLastNameFirstNameFollowup1Followup2...
1SmithJohn1/1 20051/1 2006[/font]
where patientID (pk), LastName and FirstName comes from the Patient table, and patientID (fk) and FollowupDate comes from the Followup table.
March 26, 2010 at 4:12 am
This may help you...
select
distinct t.patientid, stuff(
(
select distinct ' '+ convert(varchar(12),followupdate, 103)
from followup as t1
where t1.patientid = t.patientid
for xml path('')
),1,1,' ') from followup as t;
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 26, 2010 at 4:29 am
Thank you. I don't fully understand what's happening, but when I run the query it creates one column for all followups, and I need one column for each followup...
March 26, 2010 at 4:54 am
orca (3/26/2010)
Thank you. I don't fully understand what's happening, but when I run the query it creates one column for all followups, and I need one column for each followup...
That's strange...
AFAIK the query shouldn't run at all assuming you're using SQL2000 as mentioned... (afaik FOR XML PATH is not supported for SS2K). Please confirm what version you use.
Instead of a verbal description please provide table def, sample data and expected result as described in the first link in my signature. It would help us help you.
March 26, 2010 at 4:57 am
Is count of followupdate fixed ?
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 26, 2010 at 5:57 am
Sorry, my workstation I has SQL 2008 express installed, but the server is SQL 2000 (I'm not testing against real data).
March 26, 2010 at 7:47 am
orca (3/26/2010)
Sorry, my workstation I has SQL 2008 express installed, but the server is SQL 2000 (I'm not testing against real data).
So, on what version do you want to use the solution? SS2K or SS2K8?
Side note: remember to post sample data if you'd like to get a tested solution... 😉
March 26, 2010 at 8:39 am
Ok, I'll give it a new try, WITH the tables and some sample data.
CREATE TABLE #Patient
(
PatientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
LastName NVARCHAR(40),
FirstName NVARCHAR(40)
)
CREATE TABLE #Followup
(
FollowupID INT IDENTITY,
PatientID INT,
FollowupDate DATETIME
)
INSERT INTO #Patient VALUES ('Smith', 'James')
INSERT INTO #Patient VALUES ('Johnson', 'John')
INSERT INTO #Patient VALUES ('Williams', 'Robert')
INSERT INTO #Patient VALUES ('Jones', 'Michael')
INSERT INTO #Patient VALUES ('Brown', 'William')
INSERT INTO #Followup VALUES (1, '2008-01-01')
INSERT INTO #Followup VALUES (1, '2008-07-01')
INSERT INTO #Followup VALUES (1, '2009-01-01')
INSERT INTO #Followup VALUES (2, '2008-01-03')
INSERT INTO #Followup VALUES (2, '2008-07-03')
INSERT INTO #Followup VALUES (2, '2009-01-03')
INSERT INTO #Followup VALUES (3, '2008-01-05')
INSERT INTO #Followup VALUES (3, '2008-07-05')
INSERT INTO #Followup VALUES (3, '2009-01-05')
INSERT INTO #Followup VALUES (4, '2008-01-07')
INSERT INTO #Followup VALUES (4, '2008-07-07')
INSERT INTO #Followup VALUES (4, '2009-01-07')
INSERT INTO #Followup VALUES (5, '2008-01-09')
INSERT INTO #Followup VALUES (5, '2008-07-09')
INSERT INTO #Followup VALUES (5, '2009-01-09')
When running the the query
SELECT P.LastName, P.FirstName, CONVERT(VARCHAR(12), F.FollowupDate, 103) AS FollowupDate
FROM #Patient P
JOIN #Followup F ON P.PatientID = F.PatientID
...it lists each patients on 4 rows, one for each followup. Instead, I want every row to have only one (1) patient with LastName and FirstName AND (in this case) four additional columns (may ofcourse be more) with all the followups for each patient.
The database lives on a SS2K server (but my computer where I test the database has SS2K8 Express Edition installed). So, the code should work on SQL Server 2000.
Thanks
March 26, 2010 at 10:30 am
Ughh!
That's going to be quite a bit of work...
Do you have any chance to either use SS2K5 or above or at least get the Date values numbered for each patient within the Followup table?
Something like
INSERT INTO #Followup VALUES (1, 1, '2008-01-01')
INSERT INTO #Followup VALUES (1, 2, '2008-07-01')
INSERT INTO #Followup VALUES (1, 3, '2009-01-01')
INSERT INTO #Followup VALUES (2, 1, '2008-01-03')
INSERT INTO #Followup VALUES (2, 2, '2008-07-03')
This would make it a lot easier...
Otherwise it would require an additional intermediate table to be populated the same way your current query does (including an additional empty column). This coulmn would be used to number the followup dates as described above using a "quirky update" method. Starting with SS2K5 this could easily be done with a subquery and ROW_NUMBER() function.
After that you'd need to perform a DynamicCrossTab as describe in the related link in my signature.
So, please check if you can use SS2K5 or above for this task.
March 29, 2010 at 9:01 pm
It's not so bad, Lutz.
ROW_NUMBER() + Tally table + CASE expression = powerful dynamic SQL. 😀
-- variable number of followup dates demands dynamic SQL
declare @SQL nvarchar(max)
declare @columns nvarchar(max)=''
-- build the dynamic SQL frame
set @SQL =
';with cte1 as
(select patientid,convert(varchar(12),followupdate,101) as followupDate
,ROW_NUMBER() over(partition by patientID order by followupDate) as followupID
from #followup) -- end of cte1
select p.patientID,lastname,firstName
~tgt~
from #patient p
join cte1 c on c.patientID = p.patientID
group by p.patientID,lastname,firstname'
-- build a crosstab column for each potential row
;with tally (N) as (select top 100 ROW_NUMBER() over(order by (select null)) from sys.all_columns)
,cte1 as (select COUNT(patientID) as followups from #Followup group by PatientID)
select @columns = @columns + ' ,max(case when followupid = '+cast(N as varchar)+' then followupDate else '''' end) as Followup_'+CAST(N as varchar)+CHAR(13)
from tally
where N <= (select MAX(followups)from cte1)
order by N
-- insert the constructed columns into the dynamic SQL frame
select @SQL = REPLACE(@SQL,'~tgt~',@columns)
print @SQL -- just so we can see what is happening
-- let the games begin
exec sp_executeSQL @SQL
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2010 at 10:16 am
The Dixie Flatline (3/29/2010)
It's not so bad, Lutz.ROW_NUMBER() + Tally table + CASE expression = powerful dynamic SQL. 😀
...
I'd love to agree but, unfortunately, the requirement is:
So, the code should work on SQL Server 2000.
(see the last statement of the OP's last post...)
See what I mean? 😉
April 1, 2010 at 1:19 am
Whups.... there goes ROW_NUMBER(). Perhaps a temp table with an identity column to set up the variable number of columns in the result set? Or can we arbitrarily decide that there will never be more than 10 (or 12 or 50) followups, so we have a constant number to rely on?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 1, 2010 at 1:33 am
I have to admit that I didn't understand much of Lutz solution (but I did copy the suggestion into the Management Studio, and the result was exactly as I wanted it!), I'm quite a newbie, so I'm almost glad it didn't work...;) The help table is something I've had in mind, but haven't been able to figure how to do it.
The number of followups is different from one patient group to another, but there is ofcourse an upper limit. At the moment I don't have any patient group with more than 20 followups.
April 1, 2010 at 11:18 am
orca (4/1/2010)
I have to admit that I didn't understand much of Lutz solution (but I did copy the suggestion into the Management Studio, and the result was exactly as I wanted it!), I'm quite a newbie, so I'm almost glad it didn't work...;) The help table is something I've had in mind, but haven't been able to figure how to do it.The number of followups is different from one patient group to another, but there is ofcourse an upper limit. At the moment I don't have any patient group with more than 20 followups.
A couple things to notice:
a) It's Bobs solution (AKA The Dixie Flatline), not mine. So, it's his "reward" 😉
b) The solution he recommended works only in SS2K5 and up. So you won't be able to run it in SQL 2000 as you requested.
c) I guess it's just a typo, but your statement
the result was exactly as I wanted it! ... so I'm almost glad it didn't work...;)
is kinda strange...
d) Copy and paste solutions off the internet right into SSMS without either having a completely standalone and isolated system holding no data at all but the "internet stuff" or without completely understand what that code is going to do and how it works is not a recommended behavior at all. If there is something in a solution you don't understand or where you need a more detailed explanation, please ask. We're more than willing to provide additional explanation or at least point you to some links that might help you to understand the concept.
Okay, enough whining from my side... I'll try to set up a solution that will work for SS2K. I guess I can avoid dynamic SQL....
To be continued...
April 1, 2010 at 12:08 pm
Here's what I came up with.
The solution will display the first 6 followups. I added a note what to change if more followups are required.
Once you've studied the concept and you're familiar with it you might want to go one step further: replace the CrossTab solution with a DynamicCrossTab solution where you don't have to worry about how many followups a patient actually has and if this number would exceed the number of visits covered by the CASE statement.
The best resource I can point you at is the DynamicCrossTab link in my signature.
Edit: In the code below I used a method to calculate the row number per patient that is not the number one performance wise. But I think it's easier to start with this solution than using the "quirky update method" (which most probably is fast but is a little more complex, too...)
And here's the code I was talking about:
-- step 1: build an intermediate table to hold the data sorted as we need it
IF OBJECT_ID('tempdb..#intermed') IS NOT NULL DROP TABLE #intermed
CREATE TABLE #intermed
(
ID INT IDENTITY(1,1), -- required to "calculate" the row number per patient
PatientID INT,
LastName NVARCHAR(40),
FirstName NVARCHAR(40),
FollowupDate char(10),
Row_grp INT-- column to hold the row-number, which will be used to build the pivoted table
)
-- step 2: fill the intermediate table based on your SELECT statement, but including PatientId to ensure unique data (even for identical names)
INSERT INTO #intermed (Patientid,LastName,FirstName,FollowupDate)
SELECT P.PatientID,P.LastName, P.FirstName, CONVERT(VARCHAR(12), FollowupDate, 103)
FROM #Patient P
INNER JOIN #Followup F ON P.PatientID = F.PatientID
ORDER BY P.PatientID,F.FollowupDate
-- display the results of the insert statement for verification only
SELECT *
FROM #intermed
-- step 3: assign a row number to each row per patient ordered by FollowupDate
/*
concept:
Since we just inserted the data into a "new" table ordered by PatientID and FollowupDate
there shouldn't be any gaps within the ID column.
Therefore we can select the first row per PatientID and subtract it from the actual ID value
within each PatientID group. This will give us ascending numbers starting with 0 for each PatientID
ordered by FollowupDate. For visualization purposes I added +1 so we end up with numbers from 1 to 3 (using the data provided)
*/
UPDATE #intermed
SET row_grp = id - min_id + 1
FROM #intermed
INNER JOIN
(
SELECT MIN(id) AS min_id, patientid
FROM #intermed
GROUP BY patientid
) subquery -- this subquery return the smallest ID value per Patient
ON #intermed.patientid=subquery.patientid
-- step 4: display the final result
/*
the concept sometimes is referred to as "CrossTab". For details please read the following
article by Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/63681/
Side note: The MAX() function can be used against non-numeric data, too.
*/
SELECT
LastName,
FirstName,
MAX(CASE WHEN Row_grp=1 THEN FollowupDate ELSE '' END) AS followup_1,
MAX(CASE WHEN Row_grp=2 THEN FollowupDate ELSE '' END) AS followup_2,
MAX(CASE WHEN Row_grp=3 THEN FollowupDate ELSE '' END) AS followup_3,
MAX(CASE WHEN Row_grp=4 THEN FollowupDate ELSE '' END) AS followup_4,
MAX(CASE WHEN Row_grp=5 THEN FollowupDate ELSE '' END) AS followup_5
-- if required, add more CASE statements here
FROM #intermed
GROUP BY LastName,FirstName
ORDER BY LastName,FirstName
/* result set:
LastNameFirstNamefollowup_1followup_2followup_3followup_4followup_5
BrownWilliam09/01/200809/07/200809/01/2009
JohnsonJohn03/01/200803/07/200803/01/2009
JonesMichael07/01/200807/07/200807/01/2009
SmithJames01/01/200801/07/200801/01/2009
WilliamsRobert05/01/200805/07/200805/01/2009
*/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply