Solved

SubQuery as Comma-Delimited List

Posted on 1998-09-17
3
331 Views
Last Modified: 2012-05-04
Hello,

Is there a way to reduce a result set by putting the results of a sub query in a single column?  

For example, instead of:
Project Meeting                             Monday
Project Meeting                             Tuesday
Conference Call                             Tuesday
Conference Call                             Thursday
Conference Call                             Friday

get:
Project Meeting                             Monday, Tuesday
Conference Call                            Tuesday, Thursday, Friday

Is this possible?
0
Comment
Question by:jreilly
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
spiridonov earned 100 total points
ID: 1090155
It is not possible to do using just SQL ,however you can write a stored procedure, and use cursor to get this result.
0
 

Author Comment

by:jreilly
ID: 1090156
Do you have some quick code for this?

Thanks.
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1090157
Let's suppose table is t1,columns task and day:
Create procedure test
as
declare
@day_list  varchar(255)
@task varchar(50)
@task_prev varchar(50)

declare c_1 cursor for
select task,day
order by task

create #temp_table_1 (task varchar(50),day_list varchar(255))
open_c1
fetch next from c_1 into @task,@day
while @@fetch_status<>-1
 BEGIN
  IF (@@fetch_status <> -2)
   BEGIN      
     if (@task_prev="")
       begin
       @task_prev=@task
        @day_list=@day
     else
       if @task_prev=@task
         @day_list=@day_list+","+@day
       else
          insert into #temp_table_1   values(@task_prev,@day_list)
         @day_list=@day
    @task_prev=@task
   end
   end
fetch next from c_1 into @task,@day
END
deallocate c_1
select * from #temp_table_1


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.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

13 Experts available now in Live!

Get 1:1 Help Now