combine data in one row?

I need to display data in such a way that one row represents one Id. In
other words, I need to combine multiple rows data, separated by commas,
per each Id.
create table #temp
(a int,
b varchar(20))
insert into #temp values (1, 'green')
insert into #temp values (1, 'blue')
insert into #temp values (2, 'red')
insert into #temp values (3, 'black')
insert into #temp values (4, 'yellow')
insert into #temp values (4, 'white')
I need a query to give me this -
a b
-- --
1 green,blue
2 red
3 black
4 yellow, white
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***Check out the following thread:
http://groups.google.com/group/micr...4c4b0ff09ad4d58|||Thanks Jeff! I'll try to make it work in my case. However, I need to
make one change in the DDl. The ID fld is a varchar and it conatins
aplhanumeric values. see below the revised code:
create table #temp
(a varchar (20),
b varchar(20))
insert into #temp values ('1a', 'green')
insert into #temp values ('1a', 'blue')
insert into #temp values ('2v', 'red')
insert into #temp values ('3k', 'black')
insert into #temp values ('4x', 'yellow')
insert into #temp values ('4x', 'white')
I need a query to give me this -
a b
-- --
1a green,blue
2v red
3k black
4x yellow, white
Thanks for your help!!!
*** Sent via Developersdex http://www.examnotes.net ***|||You're kidding, right?
Just change the type of the ID column from int to varchar(20)|||Thanks, Jeff!
It is all working!
*** Sent via Developersdex http://www.examnotes.net ***

Column in contains clause?

Dear all,
Another question: I would like to get an overview of how often a number
of words, stored in a table KO, occur in a full text indexed column on
another table. I can do this seperately:
select * from names
id | name
--+--
1 | bush
2 | kerry
select count(*) from texts where contains(text, 'bush')
2,123
select count(*) from texts where contains(text, 'dean')
1,326
[numbers entiry fictional]
But since I have a large number of such names, I would like to just
join the count per name to the names table like so:
select name, count(*) from
names n, texts t
where contains(text, n.name)
group by name
but this returns an 'incorrect syntax near n'.
Why doesn't the above work? Is what I am trying to do possible using
the contains function? Is there another way to achieve this goal?
Thanks!
Wouter
It doesn't work because the Contains operator is expecting a single value
instead of a column - which is what you are passing.
I think what you need to do is something like this you might want to write a
cursor or perhaps a function that will return a table variable to accomplish
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"wouter" <wouter@.2at.nl> wrote in message
news:1108297074.753022.57670@.l41g2000cwc.googlegro ups.com...
> Dear all,
> Another question: I would like to get an overview of how often a number
> of words, stored in a table KO, occur in a full text indexed column on
> another table. I can do this seperately:
> select * from names
> id | name
> --+--
> 1 | bush
> 2 | kerry
> select count(*) from texts where contains(text, 'bush')
> 2,123
> select count(*) from texts where contains(text, 'dean')
> 1,326
> [numbers entiry fictional]
> But since I have a large number of such names, I would like to just
> join the count per name to the names table like so:
> select name, count(*) from
> names n, texts t
> where contains(text, n.name)
> group by name
> but this returns an 'incorrect syntax near n'.
> Why doesn't the above work? Is what I am trying to do possible using
> the contains function? Is there another way to achieve this goal?
> Thanks!
> Wouter
>