March 11, 2003 at 12:06 am
Hi there,
I have two tables which are a 1 - M relationship.
I have a user who want a horizontal view of the
data.
For instance,
Table A
Key-1 char(3);
table A values
_______
1
2
3
Table B
bkey-a char(3);
bcode char(1);
table b values
1 T
1 A
1 G
2 A
2 T
2 L
3 A
What he wants to see is
all matches between the tables
where the code
is "T" "A" or "L":
1, T, A
2, A, T, L
3, A
I have tried joining the tables to themselves
and doing an outer join but I end up
with multiple rows.
1, T
1, A
2 a
2 t
2 l
3 a
etc
Is this possible?
March 11, 2003 at 4:44 am
Yes it is. you would have to dynamically build the statement to get these results. I do not have a code example with me, sorry, but I have done this before so I know it is possible
March 11, 2003 at 5:04 am
I have done this through code. I was looking for a way of doing this through SQL. Its possible in Oracle using the DECODE Statement.
So I was wondering if it would be possible in SQL Server as well.
Regards,
Abhijit
March 11, 2003 at 6:09 am
I guess you could create an UDF that takes the key as a single parameter. Then in this UDF do a select to concatenate a return variable. However, this udf will be called for each row, and you must therefore eliminate duplicates. Not very good performance of course... Something like this:
SELECT DISTINCT key, myudf(key) FROM foo
myudf:
DECLARE @ret varchar(8000)
SET @ret = ''
SELECT @ret = @ret + bcode + ',' FROM foo WHERE key = @key
RETURN @ret -- (might want to remove trailing comma here)
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply