[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

show the sub-query in one line?

Posted on 2011-02-25
3
Medium Priority
?
284 Views
Last Modified: 2012-05-11
Hi everyone;

I apologize for the bad english.

I have a two tables;

 1.Table

pid   name  code
=============
1      aaa     x25
2      bbb     x26


2. Table

tid   pid    total_reserve
1     1      10
2     1      10
3     1      2
4     2      12
5     2       8

I want my sql output;
pid     name    code    total_reserve
===========================
1        aaa       x25      10,10,2
2        bbb       x26      12,8

OR


pid     name    code    total_reserve
===========================
1        aaa       x25      10                               ## row 1
                                    10      
                                     2
----------------------------------------
2        bbb       x26      12                               ## row 2
                                    8
----------------------------------------




Can I show the sub-query in one line?

Thank you for advice and assistance.

Hanifi GOKTAS
0
Comment
Question by:dmiebim
[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
3 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 34983916
try this

select pid, name, code,  (SELECT  
                              STUFF  
                              (  
                                    (  
                                    SELECT ', ' + total_reserve
                                    FROM Table2
                                                where Table2.pid = Table1.pid
                                    FOR XML PATH ('')  
                                    ),1,1,''  
                              )) as total_reserve
from Table1
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34984027
     select t2.pid, tx.name, tx.code,  STUFF(
            (
            select ','+ cast(total_reserve as varchar)
             from #table2 t1
             where t1.pid = t2.pid
             for XML path ('')),1,1,'') as  totalReserves
      from #table2 t2
            inner join table1 tx on t2.pid = tx.pid
      group by t2.pid, tx.name, tx.code            
0
 

Author Comment

by:dmiebim
ID: 34989419

ewangoya;

thank you very much for your replay. your sql true and easy, no problem.
"STUFF" : never used this command before.


tigin44;
have not tried your suggestion yet. Thank you for your return.

problem is solved
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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