March 11, 2008 at 8:09 am
how to write a query .?
clinic num doctor
Elm Valley Vet Clinic21629 MIKE
Elm Valley Vet Clinic21629 MELVIN
Elm Valley Vet Clinic21629 JAMES
the results lookslike this
the way i want c is :...........
clinic num doctor1 doctor2 doctor 3
Elm Valley Vet Clinic21629 MIKE MELVIN JAMES
how can i write the query to c like this.
March 11, 2008 at 10:09 am
Since the number of columns may change, I suggest you put your result in a temp table with the columns added in an exec statement... : )
So it would be something like :
create #Result(
clinic nvarchar(100) null,
num int null
)
-- Then count the number of required columns
declare @num int
set @num = select count(*) from your table where num = 21629
declare @count int
set @count = 1
declare @statement nvarchar(10000)
set @statement = 'alter table add '
while @count <= @num
-- Mind the coma!
begin
if @count = @num -- last one --> no noma
begin
set @statement = @statement + 'doctor' + @count + ' nvarchar(50)'
end
else
begin
set @statement = @statement + 'doctor' + @count + ' nvarchar(50),'
end
end
-- Then exec to create the table
exec statement
-- Then insert the values before updating the table
insert into #Result (clinic, num)
select clinic, num from yourtable
-- Now you need to loop again and update each column one by one
-- Reset @statement
set @statement = ''
set @count = 1
while @count < @num
begin
@statement = @statement + 'update #Result set doctor' + @count + ' =' ??
end
-- For the rest I believe you should put the data you have in the first table in another temp table, and go line by line.
-- That table should contain an int column that could be used to count each line and compare with @count attribute
Sorry for not being complete but I need to go back to work!
If necessary I'll complete this later
March 11, 2008 at 11:53 am
i tried using the code u wriitten but i thin i am issing some thing...
cant get it right...
thanks for the help
March 11, 2008 at 1:46 pm
You could try this
--Sample table and data
CREATE TABLE [DBO].[DOCS]
(ClinicName varchar(50),
ClinicNum INT,
DocName Varchar(50))
INsert INTO [DBO].[DOCS] (ClinicName,ClinicNum,DocName)
SELECT 'Elm Valley Vet Clinic',21629,'MIKE' UNION ALL
SELECT 'Elm Valley Vet Clinic',21629,'MELVIN' UNION ALL
SELECT 'Elm Valley Vet Clinic',21629,'JAMES' UNION ALL
SELECT 'Elm Valley Vet Clinic',21620,'JEO' UNION ALL
SELECT 'Elm Valley Vet Clinic',21620,'BILL' UNION ALL
SELECT 'Elm Valley Vet Clinic',21620,'BRAD'
The query will look something like this
With T AS (
select ClinicNum,ClinicName,ROW_NUMBER() OVER (PARTITION BY ClinicNum ORDER BY ClinicNum) Row,
(Select DocName + ' ' as [text()] from [DBO].[DOCS] A Where A.Clinicnum=B.Clinicnum FOR XML PATH('') ) DocNames
from [DBO].[DOCS] b)
Select T.ClinicNum,T.ClinicName,T.DocNames From T
Another way of doing this. Same results with group by
select ClinicNum,MAX(ClinicName),
(Select DocName + ' ' as [text()] from [DBO].[DOCS] A Where A.Clinicnum=B.Clinicnum FOR XML PATH('') ) DocNames
from [DBO].[DOCS] b
Group by ClinicNum
Performance wise which one would be better?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply