Solved

# Query syntax

Posted on 2014-02-18
166 Views
Hello,

Using MS SQL Server 2008.

I have a table of Measurements which represent measurements made on patients over a number of visits.

The relevant columns are:

pat_id (int) - identifies the patient
visitdate (date) - the date of the visit
value (int) - the measured value

I need to produce a query that has all a patients visits and measured values in the same row, one row per patient:

pat_id, visitdate 1, value 1, visitdate 2, value 2, visitdate 3, value 3 .... etc

I can not say for certain how many visits a patient has made.  Probably 4 or 5 but as time passes the number will increase.

Can design me a solution?
0
Question by:soozh
• 2

LVL 16

Assisted Solution

Surendra Nath earned 250 total points
ID: 39866988
check this out

declare @T TABLE
(
pat_id INT, VISITDATE DATE, VALUE INT
)

INSERT INTO @T VALUES (1,GETDATE(),2)
INSERT INTO @T VALUES (1,GETDATE()+1,4)
INSERT INTO @T VALUES (1,GETDATE()+3,7)

INSERT INTO @T VALUES (2,GETDATE(),2)
INSERT INTO @T VALUES (2,GETDATE()+1,4)
INSERT INTO @T VALUES (2,GETDATE()+3,7)
INSERT INTO @T VALUES (2,GETDATE()+4,7)

INSERT INTO @T VALUES (3,GETDATE(),2)
INSERT INTO @T VALUES (3,GETDATE()+1,4)

SELECT DISTINCT PAT_ID INTO #P FROM @T
DECLARE @MIN_PAT_ID BIGINT
DECLARE @MAX_PAT_ID BIGINT
SELECT @MIN_PAT_ID = MIN(pat_ID) FROM #P
SELECT @MAX_PAT_ID = MAX(pat_ID) FROM #P

DECLARE @T1 TABLE
(
A VARCHAR(MAX)
)

WHILE (@MIN_PAT_ID <= @MAX_PAT_ID)
BEGIN
INSERT INTO @T1
SELECT STUFF(
(SELECT ',' +CAST(PAT_ID AS VARCHAR) + ',' + CAST(VISITDATE AS VARCHAR) + ',' + CAST(VALUE AS VARCHAR)
FROM @T
WHERE pat_ID = @MIN_PAT_ID
FOR XML PATH('')),1,1,'') AS A

IF NOT EXISTS ( SELECT 1 FROM #P WHERE pat_id > @MIN_PAT_ID)
BREAK
SELECT  TOP 1 @MIN_PAT_ID =  PAT_ID FROM #P WHERE pat_id > @MIN_PAT_ID order by pat_id
END

select * FROM @T1
0

LVL 142

Expert Comment

ID: 39867070
this cannot be solved "as requested" by pure SQL.

with dynamic SQL => yes, as you will have to first run to find the number of columns you need in your output.

simple: in any case, this will not be simple
really useful: normally not.

please review (with the end user) what is really requested, because a report like this makes no practical sense, by suggesting things like below:

* you may want to list all the visits of a single patient.
* you may want to list all the patients with the number of visits, the first and last visit dates, the number of visits in the last 3 or 6 months for example
0

Author Comment

ID: 39872634

this is a one off export of data.

So i can easily find the maximum number of measurements i need to export ands write a query accordingly.

I was thinking along the lines of ranking the patients visits on date and then selecting them using a join.  There are a few more columns (100+) to export so i would like to avoid the stuff solution above - though it is elegant and would work.
0

LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 250 total points
ID: 39891339
here we go with dynamic sql, which includes a create/drop table, so please test this first as needed:
create table measurements
( pat_id int
, visitdate date
, value int
)
go
set nocount on
insert into measurements values ( 1, getdate(), 1 )
insert into measurements values ( 1, getdate()-1, 2 )
insert into measurements values ( 1, getdate()-2, 3 )
insert into measurements values ( 2, getdate(), 11 )
go
select * from measurements

declare @max_dates int
declare @loop int
declare @sql varchar(max)
select @max_dates = max(cnt)
from ( select pat_id, count(distinct visitdate) cnt from measurements group by pat_id ) sq

select @max_dates

set @sql = ' create table #export ( pat_id int '
set @loop = 1
while @loop <= @max_dates
begin
set @sql = @sql  + ', visit_date_' + cast(@loop as varchar) + ' date, value_' + cast(@loop as varchar) + ' int '
set @loop = @loop + 1
end
set @sql = @sql + ')
insert into #export ( pat_id , visit_date_1, value_1)
select pat_id , visitdate, value
from ( select pat_id , visitdate, value, row_number() over (partition by pat_id order by visitdate) rn
from measurements
) sq
where rn = 1
'

set @loop = 2
while @loop <= @max_dates
begin
set @sql = @sql + ' update e
set visit_date_' + cast(@loop as varchar) + ' = m.visitdate
, value_' + cast(@loop as varchar) + ' = m.value
from #export  e
join measurements m
on m.pat_id = e.pat_id
and m.visitdate = ( select min( x.visitdate)
from measurements x
where x.pat_id = e.pat_id
and x.visitdate > visit_date_' + cast(@loop-1 as varchar) + '
) '
set @loop = @loop + 1
end

set @sql = @sql + '
select * from #export
drop table #export
'
exec(@sql)

go
drop table measurements
0

## Featured Post

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…