Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query syntax

Posted on 2014-02-18
4
Medium Priority
?
201 Views
Last Modified: 2014-03-02
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
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 1000 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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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

by:soozh
ID: 39872634
thanks for the comments.

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question