?
Solved

SubQuery as Comma-Delimited List

Posted on 1998-09-17
3
Medium Priority
?
341 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
[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
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
spiridonov earned 400 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

800 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