Solved

show the sub-query in one line?

Posted on 2011-02-25
3
278 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
3 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to simplify my SQL statement? 14 50
Caste datetime 2 51
Query to Add Late Tolerance 10 60
Removing SQL Replication from Microsoft SQL Server 2008 R2 2 10
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

914 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now