[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Strange Query

Posted on 2002-07-12
13
Medium Priority
?
207 Views
Last Modified: 2010-05-18
I have the following Sql:

SELECT     FoliosRecibidos.Folio, Catalogo_Clientes.Descripcion AS Institucion
FROM         FoliosRecibidos INNER JOIN
                      Catalogo_Clientes ON FoliosRecibidos.IdCliente = Catalogo_Clientes.IdCliente

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.


0
Comment
Question by:AngelFireMateo
[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
  • 6
  • 6
13 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 7149735
You will need to do this with a cursor.  Let me know if you need an example.

Anthony
0
 
LVL 1

Author Comment

by:AngelFireMateo
ID: 7149737
if you have it, it would help me

thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7149808
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
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7149838
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
0
 
LVL 1

Author Comment

by:AngelFireMateo
ID: 7149917
yes  there is going to be more than one Institucion and also is in SQL 2000
0
 
LVL 1

Author Comment

by:AngelFireMateo
ID: 7149931
yes  there is going to be more than one Institucion and also is in SQL 2000
0
 
LVL 1

Author Comment

by:AngelFireMateo
ID: 7149970
yes  there is going to be more than one Institucion and also is in SQL 2000
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7150088
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  
http://www.experts-exchange.com/cplusprog/Q_20297022.html
row color in a DbGrid Date: 05/03/2002 07:49AM PST  
http://www.experts-exchange.com/delphi/Q_20296753.html
Digital Camera Date: 02/27/2002 08:51PM PST
http://www.experts-exchange.com/delphi/Q_20271715.html
storing jpg images Date: 02/19/2002 09:21PM PST
http://www.experts-exchange.com/delphi/Q_20268888.html
Batch Move with ADOTables and Normal Tables Date: 11/30/2001 07:01AM PST
http://www.experts-exchange.com/delphi/Q_20242379.html
Aggregating fields in a Table component Date: 08/28/2001 12:10PM PST
http://www.experts-exchange.com/delphi/Q_20175234.html
i install delphi in NT workstation  Date: 06/14/2001 07:20AM PST
http://www.experts-exchange.com/delphi/Q_20135405.html
sharing to other users Date: 02/20/2002 08:56PM PST
http://www.experts-exchange.com/networkgen/Q_20269302.html

Thanks,
Anthony




0
 
LVL 1

Author Comment

by:AngelFireMateo
ID: 7155212
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.Descripcion AS Institucion
FROM FoliosRecibidos
INNER JOIN  Catalogo_Clientes ON FoliosRecibidos.IdCliente = Catalogo_Clientes.IdCliente
GROUP BY FoliosRecibidos.IdCliente,Catalogo_Clientes.Descripcion, FoliosRecibidos.Folio
Open CursorFolio
FETCH NEXT FROM CursorFolio INTO @ClaveInst,@Folios,@Institucion
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...

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7155282
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
0
 
LVL 1

Author Comment

by:AngelFireMateo
ID: 7155315
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7155381
>> 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
0
 
LVL 3

Expert Comment

by:YP
ID: 7156557
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
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

656 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