column to row

[chuck]

Sometimes you start off on a path and neglect looking for alternatives. I
believe i'm on such a path and need some help.

I asked a similar question last week and got an answer that worked for
SQL2005 (using "for XML path('') ). Unfortunately, I'm stuck with using SQL
2000 for now and need a solution which will work with that.


I have a table which looks like this:

tableB:

Id col1 col2
1 1 frank
2 1 chuck
3 2 bob
4 3 steve
5 33 linda
6 2 julie


I want to be able to convert the col2 values to a single row based on the
col1 value. I also need to be able to so this from a SELECT statement so
that I can put the SELECT string as the rowsource property of an Access
listbox.

So, I would want the resulting table to look like this:

col1 col2
1 frank,chuck
2 bob,julie
3 steve
33 linda


I am able to create this stored procedure which does just that.

ALTER PROCEDURE dbo.Column_to_Row (@myval as int)
AS
DECLARE @col2 VARCHAR(1024)
SELECT @col2 = COALESCE(@col2 + ' | ','') + col2 FROM tableA WHERE col1
= @myval
SELECT col2 = @col2
RETURN

Unfortunately, I can't call a storedprocedure from within a SELECT
statement.

Perhaps I can use a function to do the same but I have not been successful
in finding the right syntax.

Help please!


If you need more information as to what I need to achieve, here goes. In
Access, I have a form with a subform. The main form is linked to TableA and
the subform is linked to TableB. The "Link Master Fields" and "Link Child
Fields" properties on the subform are set to "col1"


tableA:
col1 colA
1 blond
2 red
3 brown
33 auburn


tableB:

Id col1 col2
1 1 frank
2 1 chuck
3 2 bob
4 3 steve
5 33 linda
6 2 julie

I want to be able to output the two tables like this:

1 blond frank,chuck
2 red bob,julie
3 brown steve
33 auburn linda



Thanks

Chuck

 

[xyb]

Sometimes you start off on a path and neglect looking for alternatives. I
believe i'm on such a path and need some help.

I asked a similar question last week and got an answer that worked for
SQL2005 (using "for XML path('') ). Unfortunately, I'm stuck with using SQL
2000 for now and need a solution which will work with that.

I have a table which looks like this:

tableB:

Id col1 col2
1 1 frank
2 1 chuck
3 2 bob
4 3 steve
5 33 linda
6 2 julie

I want to be able to convert the col2 values to a single row based on the
col1 value. I also need to be able to so this from a SELECT statement so
that I can put the SELECT string as the rowsource property of an Access
listbox.

So, I would want the resulting table to look like this:

col1 col2
1 frank,chuck
2 bob,julie
3 steve
33 linda

I am able to create this stored procedure which does just that.

ALTER PROCEDURE dbo.Column_to_Row (@myval as int)
AS
DECLARE @col2 VARCHAR(1024)
SELECT @col2 = COALESCE(@col2 + ' | ','') + col2 FROM tableA WHEREcol1
= @myval
SELECT col2 = @col2
RETURN

Unfortunately, I can't call a storedprocedure from within a SELECT
statement.

Perhaps I can use a function to do the same but I have not been successful
in finding the right syntax.

Help please!

If you need more information as to what I need to achieve, here goes. In
Access, I have a form with a subform. The main form is linked to TableA and
the subform is linked to TableB. The "Link Master Fields" and "Link Child
Fields" properties on the subform are set to "col1"

tableA:
col1 colA
1 blond
2 red
3 brown
33 auburn

tableB:

Id col1 col2
1 1 frank
2 1 chuck
3 2 bob
4 3 steve
5 33 linda
6 2 julie

I want to be able to output the two tables like this:

1 blond frank,chuck
2 red bob,julie
3 brown steve
33 auburn linda

Thanks

Chuck

--tableA:
create table #tablea
(
c1 int,
c2 varchar(20)
)
insert into #tablea
select
--col1 colA
1,'blond'
union select 2,'red'
union select 3,'brown'
union select 33,'auburn'

--tableB:
create table tableb
(
id int,
c1 int,
c2 varchar(20)
)
insert into tableb
select
--Id col1 col2
1,1,'frank'
union select 2,1,'chuck'
union select 3,2,'bob'
union select 4,3,'steve'
union select 5,33,'linda'
union select 6,2,'julie'

alter function cat_byid_c1(@id int)
returns varchar(4000)
as
begin
declare @ret varchar(4000)
select @ret = ''
select @ret = @ret + c2 + '/'
from tableb
where c1 = @id
return @ret
end
go

select a.c1,a.c2,dbo.cat_byid_c1(c1)
from #tablea a

 

[Guest]

If you are looking for an Access/VBA solution, consider the generic
concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[chuck]

Thank you , thank you, thank you.

Works like a charm.

Sometimes you start off on a path and neglect looking for alternatives. I
believe i'm on such a path and need some help.

I asked a similar question last week and got an answer that worked for
SQL2005 (using "for XML path('') ). Unfortunately, I'm stuck with using
SQL
2000 for now and need a solution which will work with that.

I have a table which looks like this:

tableB:

Id col1 col2
1 1 frank
2 1 chuck
3 2 bob
4 3 steve
5 33 linda
6 2 julie

I want to be able to convert the col2 values to a single row based on the
col1 value. I also need to be able to so this from a SELECT statement so
that I can put the SELECT string as the rowsource property of an Access
listbox.

So, I would want the resulting table to look like this:

col1 col2
1 frank,chuck
2 bob,julie
3 steve
33 linda

I am able to create this stored procedure which does just that.

ALTER PROCEDURE dbo.Column_to_Row (@myval as int)
AS
DECLARE @col2 VARCHAR(1024)
SELECT @col2 = COALESCE(@col2 + ' | ','') + col2 FROM tableA WHERE
col1
= @myval
SELECT col2 = @col2
RETURN

Unfortunately, I can't call a storedprocedure from within a SELECT
statement.

Perhaps I can use a function to do the same but I have not been successful
in finding the right syntax.

Help please!

If you need more information as to what I need to achieve, here goes. In
Access, I have a form with a subform. The main form is linked to TableA
and
the subform is linked to TableB. The "Link Master Fields" and "Link Child
Fields" properties on the subform are set to "col1"

tableA:
col1 colA
1 blond
2 red
3 brown
33 auburn

tableB:

Id col1 col2
1 1 frank
2 1 chuck
3 2 bob
4 3 steve
5 33 linda
6 2 julie

I want to be able to output the two tables like this:

1 blond frank,chuck
2 red bob,julie
3 brown steve
33 auburn linda

Thanks

Chuck

--tableA:
create table #tablea
(
c1 int,
c2 varchar(20)
)
insert into #tablea
select
--col1 colA
1,'blond'
union select 2,'red'
union select 3,'brown'
union select 33,'auburn'

--tableB:
create table tableb
(
id int,
c1 int,
c2 varchar(20)
)
insert into tableb
select
--Id col1 col2
1,1,'frank'
union select 2,1,'chuck'
union select 3,2,'bob'
union select 4,3,'steve'
union select 5,33,'linda'
union select 6,2,'julie'

alter function cat_byid_c1(@id int)
returns varchar(4000)
as
begin
declare @ret varchar(4000)
select @ret = ''
select @ret = @ret + c2 + '/'
from tableb
where c1 = @id
return @ret
end
go

select a.c1,a.c2,dbo.cat_byid_c1(c1)
from #tablea a

 

[Anith Sen]

For a variety of options, see:
http://www.projectdmx.com/tsql/rowconcatenate.aspx