July 9, 2008 at 9:57 am
Hi,
I have a table with several unique entries. Let's call this table A. Each entry in table A has multiple entries in Table B. I would like to create a view that combines entries from A and B and displays them all in one row. How do i do that? Please help. Thanks.
Table A
entry 1
entry 2
entry 3
Table B
entry 1 from A entry 1 from B
entry 1 from A entry 2 from B
entry 1 from A entry 3 from B
The view I want:
entry 1 from A entry 1 from B entry 2 from B entry 3 from B
July 9, 2008 at 11:11 am
Hi,
The suggestion which i would like to you give is,
Table A is one-to-many relationship with Table B. So bring the Table B to the same cardinality of the Table A, by converting all the rows into Columns using CASE and GROUP BY based on the Primary Key or primary key of the Table A.
Now join Table A with Table B.
I hope you got the idea.
Thanks -- VJ
July 9, 2008 at 1:24 pm
You might be able to use the Pivot/Unpivot operators to do this. Take a look at those in Books Online.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2008 at 12:22 am
You will need a Function in this case...
CREATE FUNCTION [dbo].[fnJoinTabVals] ( @pTableB_ID int)
RETURNS varchar(8000)
AS
BEGIN
declare @vTableVals varchar(8000)
Select @vTableVals=COALESCE(@vTableVals+',','') + TableB_JoinColumn
FROM TableB
WHERE TableB_ID = @pTableB_ID
RETURN isnull(@vTableVals, '')
END
Now Call the Function in Your Select Statement;
Select a.TableA_ID,(Select [dbo].[fnJoinTabVals] ( a.TableA_ID)) as JoinVals from TableA a
I hope it will help you...
Atif Sheikh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply