October 20, 2004 at 11:43 am
I'm the new guy and I could use a hand. Any insight would be appreciated.
Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.
row1 x y z
row2 x b z
row3 x m z
results should look like :: x, (y + b + m) , z
Thank you in advance for any help you can provide!
October 20, 2004 at 1:43 pm
To the top!
October 20, 2004 at 1:49 pm
You need the table to have a unique key column, such as the identity column below.
create table a (i varchar(1), d varchar(10), k int identity)
insert a (i, d) values ('x', 'y')
insert a (i, d) values ('x', 'b')
insert a (i, d) values ('x', 'm')
insert a (i, d) values ('y', 'e')
insert a (i, d) values ('y', 'f')
insert a (i, d) values ('z', 'o')
select a1.i,
d =
a1.d
+ isnull(a2.d, '')
+ isnull(a3.d, '')
from a a1
full join a a2
on (a1.i = a2.i)
and (a1.k <> a2.k)
full join a a3
on (a1.i = a3.i)
and (a1.k <> a3.k)
and (a2.i = a3.i)
and (a2.k <> a3.k)
where (a1.k is not null)
and ((a1.k < a2.k) or (a2.k is null))
and ((a2.k < a3.k) or (a2.k is null) or (a3.k is null))
October 21, 2004 at 5:32 am
Thank you Grasshopper.
anyone else have any other ideas?
October 21, 2004 at 7:45 am
The data will need an identity column to specify the order of concatenation (or does this not matter?)
If the table has an identity column (eg rowid) then try
create table [mytable]
(rowid int identity(1,1), col1 char(1),col2 char(1),col3 char(1))
insert into [mytable] (col1, col2, col3) values ('x', 'y', 'z')
insert into [mytable] (col1, col2, col3) values ('x', 'b', 'z')
insert into [mytable] (col1, col2, col3) values ('x', 'm', 'z')
insert into [mytable] (col1, col2, col3) values ('w', 'd', 'v')
insert into [mytable] (col1, col2, col3) values ('w', 'e', 'v')
select a.col1, a.col2 + isnull(b.col2,'') + isnull(c.col2,'') as [col2], a.col3
from (select col1,col3,min(rowid) as [rowid] from [mytable] group by col1, col3) x
inner join [mytable] a on a.col1 = x.col1 and a.col3 = x.col3 and a.rowid = x.rowid
left outer join [mytable] b on b.col1 = x.col1 and b.col3 = x.col3 and b.rowid = x.rowid + 1
left outer join [mytable] c on c.col1 = x.col1 and c.col3 = x.col3 and c.rowid = x.rowid + 2
drop table [mytable]
Far away is close at hand in the images of elsewhere.
Anon.
October 21, 2004 at 2:38 pm
There is a UDF posted on this site that will do what you want to WITHOUT creating a table for the data. You will have to search for it because I do not remember the name of it, but I modified it and use it all the time with 3 of my web databases.
October 21, 2004 at 7:50 pm
I use a UDF to do that concatenating, also. Very useful when a bunch of different queries want to concatenate the same table column. Easy for end users and novices to get that functionality when they write querie. The permanent sql object is probably overkill for a single adhoc query, though.
October 21, 2004 at 7:54 pm
There's an example in this discussion:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=131765
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply