Need some help to write a query

[UGrad]

Hi,
Can anyone help me to figure out how to write this view in SQL 2000?
I have this table:
DoctorID PatientID
104 111
104 222
103 354
104 265
103 589

Is there an easy way to write a view based on this table and the view looks like this:
DoctorID PatientID
104 111 222 265
103 354 589

I just want to put all patient ids into one column.

 

[sqlturbo]

By having muliple values for the same record you would kill the atomicity of the PatientID column? Why would you want to do that? It'll denormalize your data and also make it slower and inefficient. However, if that is the way you want to present the data, you could do the formating on the application side and leave the structure of the database the way it is now.

 

[UGrad]

All I want is to create a summary view of doctors with their patient IDs.

I guess I am still used to the crosstab function in Access and don't know why SQL doesn't have this neat feature.

Anyway, in the worst case, I can write a stored procedure to create this table.

Thanks for your reply.

 

[SQLBill]

UGrad....

Your use of the word VIEW has caused confusion and that's why sqlturbo answered the way they did.

SQL Server uses the word VIEW to mean a 'virtual table'.

If I understand you, you want a script/query that will return this:

DoctorID PatientID
104 111 222 265
103 354 589

am I understanding what you really want?

-SQLBill

Posting advice: FAQ481-4875

 

[sqlturbo]

My bad. In a jiffy, I assumed you wanted to create a table with data stored the way you said you want to. Creating a view will not change the data structure. But, as SQLBill pointed out as well, what you are really looking for is a query with output in that format. Right?

 

[UGrad]

I am trying to write a query to format my data.

But if there is no easy way to do it, I will write a stored procedure and run it every hour to get the result I want.

Hopefully, there is an easy way.. =)

 

[SQLBill]

UGrad,

Did you bother to look at the FAQs for this forum? Take another look at FAQ183-2146.

-SQLBill

Posting advice: FAQ481-4875

 

[UGrad]

Thank you very much for posting that link.
I already have a stored procedure very similar to the one you posted.
But what I want is just a simple query.
I guess there is no way to make it.

Thanks..

 

[SQLBill]

What do you mean by 'simple query'? The loop suggestion seems pretty simple.

-SQLBill

Posting advice: FAQ481-4875

 

[UGrad]

If Access it's possible to create a simple crosstab query like this:

TRANSFORM Min(Table2.PatID) AS MinOfAMOUNT
SELECT Table2.DocID
FROM Table2
GROUP BY Table2.DocID
PIVOT [PatID];

I have a table called Table2, with 2 columns DocID and PatID:
ID DocID PatID
1 2 11
2 1 22
3 2 33
4 2 44
5 1 44
6 2 55

The above query will return:
DocID 11 22 33 44 55
1 22 44
2 11 33 44 55

I will try to find something similar in SQL.

Thanks you very much for your help.

 

[UGrad]

Problem solved:

1. Create following founction.

CREATE FUNCTION GetPatID (@DocID int)
RETURNS varchar(500)
AS
BEGIN
declare @Crsvar cursor
declare @PatID int
declare @returnstr varchar(500)
set @returnstr = ''
SET @Crsvar = Cursor for SELECT PatID FROM Table2 where DocID = @DocID
OPEN @Crsvar
FETCH NEXT FROM @CrsVar INTO @PatID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
set @returnstr = @returnstr + cast(@PatID as varchar(10)) + ', '

FETCH NEXT FROM @CrsVar INTO @PatID
END
return (@returnstr)
END

2. Write a query.
select DocID, GetPatID(DocID) from Table2