Join?

I have the following two tables of data:
Answer QuestionID CustomerID
-- -- --
Answer1 15 5090
Answer2 14 5090
Answer3 14 5090
Answer4 15 5091
Answer5 14 5091
Answer6 14 5091
CustomerID IEventID IEDescription
-- -- --
5090 1 TEST1
5091 2 TEST2
As you can see there is a similar field between the two tables (CustomerID).
My problem is that I need to merge these two tables into one table. I could
use a simple join statement but then I would end up with 6 unique records. I
need two unique records - something like this:
IECustomerID IEventID IEDescription ? ?
?
-- -- -- --
-- --
5090 1 TEST1 Answer1
Answer2 Answer3
5091 2 TEST2 Answer4
Answer5 Answer6
I want to output the results using an ASP.NET repeater. Is there a way to do
what I want? Am I looking at this in the wrong way? Or is this just
impossible? Any guidance or assistance anyone could provide would be
appreciated.
Thank You,
BradAs far as I can tell all you need is...
SELECT t1.Answer , t1.QuestionID , t1.CustomerID
, t2.CustomerID, t2.IEventID, t2.IEDescription
FROM customerquestions t1 FULL OUTER JOIN t2
ON ( t1.CustomerID=t2.CustomerID)
I do not know what your table names are so i Put in some guesses..|||You'll need to use a pivot function for this. But, there is a problem with
the table design. The problem is that there is no way to distinguish
between Answer2 and Answer3, also the same problem exists for Answer5 &
Answer6. Once you sort that problem out perfoming a pivot will be easy.
To perform the Pivot either use the PIVOT command in SQL2005, or in SQL2000
use syntax like this...
Select
CustomerID,
Max( Case When QuestionID = 15 then Answer Else null ) AnswerToQestion15,
Max( Case When QuestionID = 14 then Answer Else null )
swerToQestion14, -- note: This will only return ONE answer.
From Table1
Group By CustomerID
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:OwO2o10ZGHA.3832@.TK2MSFTNGP04.phx.gbl...
>I have the following two tables of data:
> Answer QuestionID CustomerID
> -- -- --
> Answer1 15 5090
> Answer2 14 5090
> Answer3 14 5090
> Answer4 15 5091
> Answer5 14 5091
> Answer6 14 5091
> CustomerID IEventID IEDescription
> -- -- --
> 5090 1 TEST1
> 5091 2 TEST2
>
> As you can see there is a similar field between the two tables
> (CustomerID). My problem is that I need to merge these two tables into one
> table. I could use a simple join statement but then I would end up with 6
> unique records. I need two unique records - something like this:
> IECustomerID IEventID IEDescription ?
> ? ?
> -- -- -- --
> -- --
> 5090 1 TEST1 Answer1
> Answer2 Answer3
> 5091 2 TEST2 Answer4
> Answer5 Answer6
> I want to output the results using an ASP.NET repeater. Is there a way to
> do what I want? Am I looking at this in the wrong way? Or is this just
> impossible? Any guidance or assistance anyone could provide would be
> appreciated.
> Thank You,
> Brad
>