I'm trying to merge to records. All of the fields are the same except
for dbo.MaterialProducedActualEpa.EpaName. I'm able to use a 2nd
field to determine which one I want. Is it possible to show both of
them in 1 record? The following try may help to understand what I'm
trying to achieve.


SELECT
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then
ProdDB.dbo.MaterialProducedActualEpa.EpaName END) AS Expr1,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then
ProdDB.dbo.MaterialProducedActualEpa.EpaValue END) AS Model1,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'FinalPartDesc' Then ProdDB.dbo.MaterialProducedActualEpa.EpaValue
END) AS Model2,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.GenealogyId
END) AS MFG_SN,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.EpaName END)
AS Expr2,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.EpaValue END)
AS Expr3,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.EventDate END)
AS Expr4,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then
ProdDB.dbo.ResourceActualEpa.ProcessSegmentId END) AS Location,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttribu teName =
N'InnerOuterPartDesc' Then
RIGHT(ProdDB.dbo.ResourceActualEpa.SegmentResponse Id, 14) END) AS
Timestamp
FROM ProdDB.dbo.ResourceActualEpa INNER JOIN
ProdDB.dbo.MaterialProducedActualEpa ON
ProdDB.dbo.ResourceActualEpa.GenealogyId =
ProdDB.dbo.MaterialProducedActualEpa.GenealogyId
WHERE (ProdDB.dbo.ResourceActualEpa.EventDate > CONVERT(DATETIME,
'2010-04-05 00:00:00', 102)) AND
(ProdDB.dbo.ResourceActualEpa.GenealogyId =
N'127927') AND (ProdDB.dbo.MaterialProducedActualEpa.EpaName =
N'PartDescription' OR
ProdDB.dbo.MaterialProducedActualEpa.EpaName IS
NULL) AND (ProdDB.dbo.ResourceActualEpa.EpaName = N'Leak1')
ORDER BY MFG_SN

With Case Statements
NULL NULL FNL XC20 MILLENNIUM NULL NULL NULL NULL NULL NULL
PartDescription I/O MILLENNIUM 20 LITER NULL 127927 Leak1 NO LEAK
2010-04-19 13:52:56.983 Inner Mass Spec. 20100419135242

Without
PartDescription I/O MILLENNIUM 20 LITER 127927 Leak1 NO LEAK
2010-04-19 13:52:56.983 Inner Mass Spec. 20100419135242
PartDescription I/O MILLENNIUM 20 LITER 127927 Leak1 NO LEAK
2010-04-19 13:52:56.983 Inner Mass Spec. 20100419135242

mcolson (mcolson1590 (AT) gmail (DOT) com) writes:
I'm afraid that I feel quite clueless. You have two tables,
MaterialProducedActualEpa and ResourceActualEpa. You join them over
GenealogyId. Is that column a primary key in both tables? No, that
does not seem likely, given your WHERE condition.

I don't really know you mean with show "both of them in one record".
Do you want to display rows from both tables as one row in the output?


Since news tends to wrap lines at a width of 80 characters, this is
difficult to read and understand. Even less do I know where the output
"without" comes from. Or what result you really want.

In short, I think you need to explain a little more in detail, what
you are trying to achieve.


And, by the way, there are no CASE statements in SQL, but well a
CASE expression.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx