concatenate a column from multiple rows

Hello,

   I am not sure if its right to post it here, I would like
to know the SQL to concatenate a column from multiple rows
in an object?? I am using TransactSQL.

I have an object with below records

id    name
---    ----
1      raj
2      abc
3      xyz
1      red
2      green
3      pink

I need the ourput like below

id   name
---  ----
1   raj,red
2   abc,green
3   xyz,pink

Thanks in advance
Raj
0
Raj
8/9/2010 2:18:19 AM
sybase.ase.general 8655 articles. 0 followers. Follow

6 Replies
6438 Views

Similar Articles

[PageSpeed] 2

<Raj> wrote in message news:4c5f656b.2e5f.1681692777@sybase.com...
> Hello,
>
>   I am not sure if its right to post it here, I would like
> to know the SQL to concatenate a column from multiple rows
> in an object?? I am using TransactSQL.
>
> I have an object with below records
>
> id    name
> ---    ----
> 1      raj
> 2      abc
> 3      xyz
> 1      red
> 2      green
> 3      pink
>
> I need the ourput like below
>
> id   name
> ---  ----
> 1   raj,red
> 2   abc,green
> 3   xyz,pink
>
> Thanks in advance
> Raj


Your table appears to be very denormalised.  (Bad design?)  The following 
does the trick but could be very slow with a large table.  And I wouldn't 
want to generalize to more than two columns being concatenated.


create table #test
  (id    tinyint       not null,
   name  varchar (10)  not null)

insert into #test  values (1, 'raj')
insert into #test  values (2, 'abc')
insert into #test  values (3, 'xyz')
insert into #test  values (1, 'red')
insert into #test  values (2, 'green')
insert into #test  values (3, 'pink')

create table #test2
  (id       tinyint            not null,
   name  varchar (10)  not null,
   seq     tinyint            identity)

insert into #test2
   select *
   from #test

select *
from #test2

select t1.name  ||  ', '  ||  t2.name
from #test2 t1,
       #test2 t2
where t1.id  = t2.id
    and t1.seq = (select min (t3.seq)
                          from #test2 t3
                          where t3.id = t1.id)
    and t2.seq = (select max (t4.seq)
                          from #test2 t4
                          where t4.id = t1.id)
group by t1.id
having t1.id  = t2.id
    and t1.seq = (select min (t3.seq)
                          from #test2 t3
                           where t3.id = t1.id)
    and t2.seq = (select max (t4.seq)
                          from #test2 t4
                          where t4.id = t1.id)
order by t1.seq

 id  name       seq
 --- ---------- --- 
   1 raj          1
   2 abc          2
   3 xyz          3
   1 red          4
   2 green        5
   3 pink         6

 ---------------------- 
 raj, red
 abc, green
 xyz, pink




1
Carl
8/10/2010 11:43:34 AM
On 8/8/2010 8:18 PM, Raj wrote:
> Hello,
>
>     I am not sure if its right to post it here, I would like
> to know the SQL to concatenate a column from multiple rows
> in an object?? I am using TransactSQL.
>
> I have an object with below records
>
> id    name
> ---    ----
> 1      raj
> 2      abc
> 3      xyz
> 1      red
> 2      green
> 3      pink
>
> I need the ourput like below
>
> id   name
> ---  ----
> 1   raj,red
> 2   abc,green
> 3   xyz,pink
>
> Thanks in advance
> Raj


Is there any requirement on the order of the concatenated values?
In your example results, the order is alphabetical for ids 1 and 2
but not for 3.

Is there some known maximum number of values for each id?

If there is no reasonably small limit, then using a cursor might
actually be the best solution.

0
Bret
8/10/2010 3:21:02 PM
Or - get a copy of SQLAnywhere, which has the List() aggregate on strings.

Select id, list( name ) from table
group by id ;

Submit an enhancement request to add this into ASE...

-- 
Paul Horan[Sybase]
http://paulhoran.ulitzer.com

<Raj> wrote in message news:4c5f656b.2e5f.1681692777@sybase.com...
> Hello,
>
>   I am not sure if its right to post it here, I would like
> to know the SQL to concatenate a column from multiple rows
> in an object?? I am using TransactSQL.
>
> I have an object with below records
>
> id    name
> ---    ----
> 1      raj
> 2      abc
> 3      xyz
> 1      red
> 2      green
> 3      pink
>
> I need the ourput like below
>
> id   name
> ---  ----
> 1   raj,red
> 2   abc,green
> 3   xyz,pink
>
> Thanks in advance
> Raj 


0
Paul
8/12/2010 7:39:50 PM
"Paul Horan[Sybase]" <phoran_remove@remove_sybase.com> wrote in message 
news:4c644e06$1@forums-1-dub...
> Or - get a copy of SQLAnywhere, which has the List() aggregate on strings.
>
> Select id, list( name ) from table
> group by id ;
>
> Submit an enhancement request to add this into ASE...
>

Already submitted as ISUG ER 2042 a long time ago by ... Paul Horan.  It 
looks like ASE does not plan to implement it.

> -- 
> Paul Horan[Sybase]
> http://paulhoran.ulitzer.com
>
> <Raj> wrote in message news:4c5f656b.2e5f.1681692777@sybase.com...
>> Hello,
>>
>>   I am not sure if its right to post it here, I would like
>> to know the SQL to concatenate a column from multiple rows
>> in an object?? I am using TransactSQL.
>>
>> I have an object with below records
>>
>> id    name
>> ---    ----
>> 1      raj
>> 2      abc
>> 3      xyz
>> 1      red
>> 2      green
>> 3      pink
>>
>> I need the ourput like below
>>
>> id   name
>> ---  ----
>> 1   raj,red
>> 2   abc,green
>> 3   xyz,pink
>>
>> Thanks in advance
>> Raj
>
> 


0
Carl
8/14/2010 12:59:35 PM
> On 8/8/2010 8:18 PM, Raj wrote:
> > Hello,
> >
> >     I am not sure if its right to post it here, I would
> > like to know the SQL to concatenate a column from
> > multiple rows in an object?? I am using TransactSQL.
> >
> > I have an object with below records
> >
> > id    name
> > ---    ----
> > 1      raj
> > 2      abc
> > 3      xyz
> > 1      red
> > 2      green
> > 3      pink
> >
> > I need the ourput like below
> >
> > id   name
> > ---  ----
> > 1   raj,red
> > 2   abc,green
> > 3   xyz,pink
> >
> > Thanks in advance
> > Raj
>
>
> Is there any requirement on the order of the concatenated
> values? In your example results, the order is alphabetical
> for ids 1 and 2 but not for 3.
>
> Is there some known maximum number of values for each id?
>
> If there is no reasonably small limit, then using a cursor
> might actually be the best solution.
>

Order is not really matter. NO, don't know the maximum no.of
values for each id. ok.
0
Rajasekhar
8/17/2010 6:20:43 AM
> <Raj> wrote in message
> > news:4c5f656b.2e5f.1681692777@sybase.com... Hello,
> >
> >   I am not sure if its right to post it here, I would
> > like to know the SQL to concatenate a column from
> > multiple rows in an object?? I am using TransactSQL.
> >
> > I have an object with below records
> >
> > id    name
> > ---    ----
> > 1      raj
> > 2      abc
> > 3      xyz
> > 1      red
> > 2      green
> > 3      pink
> >
> > I need the ourput like below
> >
> > id   name
> > ---  ----
> > 1   raj,red
> > 2   abc,green
> > 3   xyz,pink
> >
> > Thanks in advance
> > Raj
>
>
> Your table appears to be very denormalised.  (Bad design?)
>  The following  does the trick but could be very slow with
> a large table.  And I wouldn't  want to generalize to more
> than two columns being concatenated.
>
>
> create table #test
>   (id    tinyint       not null,
>    name  varchar (10)  not null)
>
> insert into #test  values (1, 'raj')
> insert into #test  values (2, 'abc')
> insert into #test  values (3, 'xyz')
> insert into #test  values (1, 'red')
> insert into #test  values (2, 'green')
> insert into #test  values (3, 'pink')
>
> create table #test2
>   (id       tinyint            not null,
>    name  varchar (10)  not null,
>    seq     tinyint            identity)
>
> insert into #test2
>    select *
>    from #test
>
> select *
> from #test2
>
> select t1.name  ||  ', '  ||  t2.name
> from #test2 t1,
>        #test2 t2
> where t1.id  = t2.id
>     and t1.seq = (select min (t3.seq)
>                           from #test2 t3
>                           where t3.id = t1.id)
>     and t2.seq = (select max (t4.seq)
>                           from #test2 t4
>                           where t4.id = t1.id)
> group by t1.id
> having t1.id  = t2.id
>     and t1.seq = (select min (t3.seq)
>                           from #test2 t3
>                            where t3.id = t1.id)
>     and t2.seq = (select max (t4.seq)
>                           from #test2 t4
>                           where t4.id = t1.id)
> order by t1.seq
>
>  id  name       seq
>  --- ---------- ---
>    1 raj          1
>    2 abc          2
>    3 xyz          3
>    1 red          4
>    2 green        5
>    3 pink         6
>
>  ----------------------
>  raj, red
>  abc, green
>  xyz, pink
>
>
>
>


Thanks.
0
Raj
8/17/2010 6:24:03 AM
Reply:

Similar Artilces:

Concatenate multiple rows into one column?
Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:TableParent (ASSIGNNUM (PK), DESC, STARTDATE)TableChild (ASSIGNNUM (FK), EMPLOYEENUM)There could be multiple employees for each assignment. Sample data:TableParent1....First Assignment....05/01/20082....Second Assignment...05/03/20083....Third Assignment....05/07/2008TableChild1....553422....334562....523433....352253....451213....11553I would like t...

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

Concatenate varchar columns across multiple rows
Hi all, Is it possible in SQL to use the GROUP BY clause (or some other built in SQL clause) to SUM not only numbers but also strings so the result is a concatenated string. So the query might look like...SELECT myGroupCode, SUM(Surname + ', ') As ConcatenatedSurnames FROM tblPeople GROUP BY myGroupCodeThe result returned in column ConcatenatedSurnames is..."Jordan, Bird, Johnson, Erving"Is there a simple way of doing this?Thanks for your time.  select distinct myGroupCode, stuff((select ',' + SurName from tblPeople where myGroupCode = t.myGroupCode),1,1,...

How can I display multiple rows w/8 columns (4 columns editable on each row)?
Hello, Please - Can anyone suggest the best way to display a joined dataset with 4 read-only columns, 4 editable columns and a checkbox column? The content for this display currently has < 100 rows - but could approach 1000 in the future. I currently have this setup with a Datagrid (working) - but I would like to be able to edit the 4 columns of ALL rows in the displayed list instead of one row at a time as the Datagrid allows. After editing, I use an update function to traverse the modified list/table and save all values that have the Checkbox clicked. Could a list with 10...

Arranging data on multiple rows into a sigle row (converting rows into columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on...

Multiple rows to multiple columns in oracle
Hello, Please help.... tbl_studentstdid stdname 1       X 2       Y 3       Z tbl_Subjects stdid subjects   Marks 1       Maths      80% 1      Science    60% 1      History     75% 2      Science    50% 2      Chemistry 90% 3      Sports&nbs...

concatenate multiple rows in one row
i have a table below id    text1    text2 -------------------------------118   id12    text123 7118   4     retre 8118     3    erwetre    i need to convert the rows to columns in sql server 2000(pivot in 2005 but i dont want in 2005) to id  text1   text2            text1        text2   text1       &...

Datalist item span multiple columns, multiple rows (rowspan, colspan)
I'm currently using a DataList to display a list of photos. Generates a 3 x 3 table.However, what I would like to do is take up the first 2 cells on the right column to display an ad in there.  This will mean that instead of displaying 9 items, I'm now only displaying 7 items.I'm looking for something like rowspan so that the top right cell will have a rowspan of 2.  I've tried looking at the DataList's ItemDataBound but can't find anything.Am I using the correct control for this task?Thank you, - Andy Should I just be using Table, TableRow ...

How to go through a datatable row by row and column by column
Hello, I have a dataset with a datatable and I need to insert each row from the datatable on my sql table, anyway there may be errors, so some rows may not be inserted because primary key constraints,  So I need to show the rows that are not inserted in a gridview, the hard thing to do is to go through all the datatable,  is there an wasy way to do it?MCPD ENTERPRISE APPLICATION DEVELOPER Start by looking herehttp://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspxPerform an update of your dataset, and if a row fails, you can mark it as invalid.You can ...

Concatenation of multiple rows
Can someone please help me out in writing the Sql to concatenate the Text_desc for each code for all the seq_nos in the ascending order of seq_no and load into the target table(T1). Please see the sample data below in the Source(S1) and the expected result in the target(T1). There are about 2,30,000 records in S1 and the seq_no can be anything. Source table : S1 Code Seq_no Text_desc --------------------------- 1579 0 BCN5, CO20, ER75 1579 1 FP5, WC6, MATW, OPRH 1579 2 T 1600 0 TW, FP5, AS5, MHSA15, DME20 1600 1 ER100, U...

Concatenate Rows and Columns
Hi,... I like to know how can i concatenate some value for columns in one column, i know that i can make a 'Computed Field and write this expression 'String (col1) + String (col2) + ...' But the problem how can i concatenate some values for rows under condition -- Thanks... O s a m a D i a b diab98@hotmail.com Well let's see if I understand you right. You want to concatenate rows under certain conditions, for example react on the case that an item is null? In a computed field you can use a lot of functions. One of them is "if"...

Gridview
Hi. What I would like to do is to have a layout like this ID Description SomeOthercolumn ----------------------------------------------------------------------------- ID Short description Text Some other value Long description Text that will span multiple columns ID #2 Short description Text #2 Some other value #2 Long description Text that will span multiple columns #2 Get the picture. This is not a problem with repeaters, because you ha...

Merging DataTables with multiple columns
I have 2 datatablesTable 0:Col Time    Col A    Col BTable 1:Col Time    Col C    Col DDesired Merged Table Result (final Table 0):Cole Time  Col A  Col B  Col C  Col D(with all same Time rows in the same row)   Via merge, I would like a merged table where all rows from either table with the same time (my PK column) all appear on the same row.  The table structure does get output correctly, but those rows with identical times from Table 1 (and 2, 3, etc.)  appear as an additional row...

Modify column in multiple rows, but not all!
Hi all. Code in clicked event of dw. ls_name = dwo.name ls_modstring = ls_name + ".expression='bitmap ( if ( GetRow() = " + string(row) + ", ~".\select.bmp~", ~".\unselect.bmp~" ) )'" .... ls_ret = this.modify ( ls_modstring ) Code works, but I can only 'select' one row. I want to select multiple rows and mark them with 'select.bmp'. (PB 7.0.2) Best regards Trond Nordland Instead of using "GetRow()=n", use "IsSelected()" -- Simon Caldwell Get Real Systems Ltd Holtby Manor, Stamf...

Web resources about - concatenate a column from multiple rows - sybase.ase.general

concatenate - Online Journalism Blog
Posts about concatenate written by Paul Bradshaw

Concatenate: The Ultimate Excel Function for Managing PPC Campaigns
... build URL tags. There are lots of Excel functions out there that can make managing and reporting on PPC campaigns easier. My favorite is Concatenate ...

Resources last updated: 2/10/2016 4:28:09 AM