AngelFireMateo
asked on
Strange Query
I have the following Sql:
SELECT FoliosRecibidos.Folio, Catalogo_Clientes.Descripc ion AS Institucion
FROM FoliosRecibidos INNER JOIN
Catalogo_Clientes ON FoliosRecibidos.IdCliente = Catalogo_Clientes.IdClient e
and the result is this
Folio Institucion
1 HOSPITAL MIGUEL HIDALGO
5 HOSPITAL MIGUEL HIDALGO
8 HOSPITAL MIGUEL HIDALGO
15 HOSPITAL MIGUEL HIDALGO
25 HOSPITAL MIGUEL HIDALGO
What I want to do is to show the results in the following way:
Institucion Folio
HOSPITAL MIGUEL HIDALGO 1,5,8,15,25
thanks in advance
sincerely
Ricardo.
SELECT FoliosRecibidos.Folio, Catalogo_Clientes.Descripc
FROM FoliosRecibidos INNER JOIN
Catalogo_Clientes ON FoliosRecibidos.IdCliente = Catalogo_Clientes.IdClient
and the result is this
Folio Institucion
1 HOSPITAL MIGUEL HIDALGO
5 HOSPITAL MIGUEL HIDALGO
8 HOSPITAL MIGUEL HIDALGO
15 HOSPITAL MIGUEL HIDALGO
25 HOSPITAL MIGUEL HIDALGO
What I want to do is to show the results in the following way:
Institucion Folio
HOSPITAL MIGUEL HIDALGO 1,5,8,15,25
thanks in advance
sincerely
Ricardo.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this on SQL2000 (in which case we can use a function, otherwise we will have to use a temporary table) and I assume there will be more than one Institucion, as in:
HOSPITAL MIGUEL HIDALGO 1,5,8,15,25
HOSPITAL BENITO JUAREZ 3,9,12,18,32
Antony
HOSPITAL MIGUEL HIDALGO 1,5,8,15,25
HOSPITAL BENITO JUAREZ 3,9,12,18,32
Antony
I am sorry, that may not have been very clear. But essentially they were two questions:
1. Is this on SQL2000?
2. I assume there will be more than one Institucion?
Anthony
1. Is this on SQL2000?
2. I assume there will be more than one Institucion?
Anthony
ASKER
yes there is going to be more than one Institucion and also is in SQL 2000
ASKER
yes there is going to be more than one Institucion and also is in SQL 2000
ASKER
yes there is going to be more than one Institucion and also is in SQL 2000
You have way to many open questions, can you take care of them, while I come up with a solution? For the record, here are your open questions:
printing a char in graphics mode Date: 05/04/2002 08:47AM PST
https://www.experts-exchange.com/questions/20297022/printing-a-char-in-graphics-mode.html
row color in a DbGrid Date: 05/03/2002 07:49AM PST
https://www.experts-exchange.com/questions/20296753/row-color-in-a-DbGrid.html
Digital Camera Date: 02/27/2002 08:51PM PST
https://www.experts-exchange.com/questions/20271715/Digital-Camera.html
storing jpg images Date: 02/19/2002 09:21PM PST
https://www.experts-exchange.com/questions/20268888/storing-jpg-images.html
Batch Move with ADOTables and Normal Tables Date: 11/30/2001 07:01AM PST
https://www.experts-exchange.com/questions/20242379/Batch-Move-with-ADOTables-and-Normal-Tables.html
Aggregating fields in a Table component Date: 08/28/2001 12:10PM PST
https://www.experts-exchange.com/questions/20175234/Aggregating-fields-in-a-Table-component.html
i install delphi in NT workstation Date: 06/14/2001 07:20AM PST
https://www.experts-exchange.com/questions/20135405/i-install-delphi-in-NT-workstation.html
sharing to other users Date: 02/20/2002 08:56PM PST
https://www.experts-exchange.com/questions/20269302/sharing-to-other-users.html
Thanks,
Anthony
printing a char in graphics mode Date: 05/04/2002 08:47AM PST
https://www.experts-exchange.com/questions/20297022/printing-a-char-in-graphics-mode.html
row color in a DbGrid Date: 05/03/2002 07:49AM PST
https://www.experts-exchange.com/questions/20296753/row-color-in-a-DbGrid.html
Digital Camera Date: 02/27/2002 08:51PM PST
https://www.experts-exchange.com/questions/20271715/Digital-Camera.html
storing jpg images Date: 02/19/2002 09:21PM PST
https://www.experts-exchange.com/questions/20268888/storing-jpg-images.html
Batch Move with ADOTables and Normal Tables Date: 11/30/2001 07:01AM PST
https://www.experts-exchange.com/questions/20242379/Batch-Move-with-ADOTables-and-Normal-Tables.html
Aggregating fields in a Table component Date: 08/28/2001 12:10PM PST
https://www.experts-exchange.com/questions/20175234/Aggregating-fields-in-a-Table-component.html
i install delphi in NT workstation Date: 06/14/2001 07:20AM PST
https://www.experts-exchange.com/questions/20135405/i-install-delphi-in-NT-workstation.html
sharing to other users Date: 02/20/2002 08:56PM PST
https://www.experts-exchange.com/questions/20269302/sharing-to-other-users.html
Thanks,
Anthony
ASKER
Well I have the cursor done but It gives to me some strange results
Create table #Temp(Clave varchar(3),Institucion varchar(50), folios varchar(150))
go
Use ControlCercNac
Declare @s varchar(150),@ClaveInst varchar(3),@Institucion varchar(50),@Folios float
Declare CursorFolio Cursor For
SELECT FoliosRecibidos.IdCliente, FoliosRecibidos.Folio, Catalogo_Clientes.Descripc ion AS Institucion
FROM FoliosRecibidos
INNER JOIN Catalogo_Clientes ON FoliosRecibidos.IdCliente = Catalogo_Clientes.IdClient e
GROUP BY FoliosRecibidos.IdCliente, Catalogo_C lientes.De scripcion, FoliosRecibidos.Folio
Open CursorFolio
FETCH NEXT FROM CursorFolio INTO @ClaveInst,@Folios,@Instit ucion
While @@Fetch_Status <> -1
Begin
select @s = ''
select @s = coalesce(@s,'') + str(Folio,6,0) + ',' from FoliosRecibidos where IdCliente = @ClaveInst
Insert into #Temp values( @ClaveInst,@Institucion, left(@s,datalength(@s)-1))
FETCH CursorFolio INTO @ClaveInst, @Folios,@Institucion
End
Close CursorFolio
Deallocate CursorFolio
Select * from #Temp
drop table #Temp
Go
These are the results
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
103 HEGO 8000, 8004, 8005
103 HEGO 8000, 8004, 8005
103 HEGO 8000, 8004, 8005
and I want that appears something like this:
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
103 HEGO 8000, 8004, 8005
thanks...
Create table #Temp(Clave varchar(3),Institucion varchar(50), folios varchar(150))
go
Use ControlCercNac
Declare @s varchar(150),@ClaveInst varchar(3),@Institucion varchar(50),@Folios float
Declare CursorFolio Cursor For
SELECT FoliosRecibidos.IdCliente,
FROM FoliosRecibidos
INNER JOIN Catalogo_Clientes ON FoliosRecibidos.IdCliente = Catalogo_Clientes.IdClient
GROUP BY FoliosRecibidos.IdCliente,
Open CursorFolio
FETCH NEXT FROM CursorFolio INTO @ClaveInst,@Folios,@Instit
While @@Fetch_Status <> -1
Begin
select @s = ''
select @s = coalesce(@s,'') + str(Folio,6,0) + ',' from FoliosRecibidos where IdCliente = @ClaveInst
Insert into #Temp values( @ClaveInst,@Institucion, left(@s,datalength(@s)-1))
FETCH CursorFolio INTO @ClaveInst, @Folios,@Institucion
End
Close CursorFolio
Deallocate CursorFolio
Select * from #Temp
drop table #Temp
Go
These are the results
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
103 HEGO 8000, 8004, 8005
103 HEGO 8000, 8004, 8005
103 HEGO 8000, 8004, 8005
and I want that appears something like this:
101 HOSPITAL MIGUEL HIDALGO 1,5,8,15,25,30,40,50,66
103 HEGO 8000, 8004, 8005
thanks...
Do you intend to maintain your open questions? For the record:
Questions Asked 21
Last 10 Grades Given B B A B B B B A B B
Question Grading Record 13 Answers Graded / 13 Answers Received
Anthony
Questions Asked 21
Last 10 Grades Given B B A B B B B A B B
Question Grading Record 13 Answers Graded / 13 Answers Received
Anthony
ASKER
I've close some questions and others I haven't received answer, I can't give the points to someone that hadn't satisfied my questions.
>> I've close some questions and others I haven't received answer <<
Correction: Out of 8 questions, you awarded a "B" to two of the questions and ignored the rest. Your questions are your responsibility, if you choose not to follow-up on them and abandon them, then do not expect Experts to be very receptive.
It is your decision and I wish you the very best of luck.
Anthony
Correction: Out of 8 questions, you awarded a "B" to two of the questions and ignored the rest. Your questions are your responsibility, if you choose not to follow-up on them and abandon them, then do not expect Experts to be very receptive.
It is your decision and I wish you the very best of luck.
Anthony
Please pay attention to acperkins comments and close the questions ! Open questions do make a mess of this wonderful forum.
To solve this problem ...
you could use
Select Distinct * from #Temp
instead of
Select * from #Temp
Best regards
YP
To solve this problem ...
you could use
Select Distinct * from #Temp
instead of
Select * from #Temp
Best regards
YP
ASKER
thanks