Prev: Whitepaper - In-Memory Analytics
Next: Yellowfin 5.0 Webinar
From: mcolson on 27 Apr 2010 11:44
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.SourceAttributeName =
N'InnerOuterPartDesc' Then
ProdDB.dbo.MaterialProducedActualEpa.EpaName END) AS Expr1,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'InnerOuterPartDesc' Then
ProdDB.dbo.MaterialProducedActualEpa.EpaValue END) AS Model1,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'FinalPartDesc' Then ProdDB.dbo.MaterialProducedActualEpa.EpaValue
END) AS Model2,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.GenealogyId
END) AS MFG_SN,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.EpaName END)
AS Expr2,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.EpaValue END)
AS Expr3,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'InnerOuterPartDesc' Then ProdDB.dbo.ResourceActualEpa.EventDate END)
AS Expr4,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'InnerOuterPartDesc' Then
ProdDB.dbo.ResourceActualEpa.ProcessSegmentId END) AS Location,
(CASE WHEN ProdDB.dbo.MaterialProducedActualEpa.SourceAttributeName =
N'InnerOuterPartDesc' Then
RIGHT(ProdDB.dbo.ResourceActualEpa.SegmentResponseId, 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
From: Erland Sommarskog on 27 Apr 2010 17:31
mcolson (mcolson1590(a)gmail.com) writes:
> 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.

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?


> 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

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Whitepaper - In-Memory Analytics
Next: Yellowfin 5.0 Webinar