Archives of the TeradataForum

Message Posted: Sun, 23 Apr 2006 @ 10:53:15 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Help needed for writing SQL

From:		Dieter Noeth

Frank.C.Martinez wrote:

Well, this might be a made a little more difficult by the fact that the MemID doesn't seem to be unique for the names you want. But if the data is actually like:

          > MemID  Name  Value
          > 1      A     Good
          > 1      B     Better
          > 2      A     
          > 3      B     Wurst (for Dieter's sake)

I don't think, that Wurst is reasonable during a diet, maybe the low-fat one. Btw, i'd prefer Bratwurst :-)

          > SELECT MemID,
          >        CASE
          >           WHEN Value IS NULL
          >              THEN t1.Name
          >              ELSE t2.Name
          >         END AS Name,
          >        COALESCE(t1.Value, t2.Value) AS Value
          >   FROM TheTable t1
          >   JOIN TheTable t2 -- Could be an left outer join if there's
          > not always a B for an A
          >     ON t1.MemID = t2.MemID
          >    AND t2.Name = 'B'
          >  WHERE t1.Name = 'A';

Another one without a self-join:

     SELECT
        MemID,
        COALESCE(MAX(CASE WHEN Name = 'A' THEN Name END),
                 MAX(CASE WHEN Name = 'B' THEN Name END)
                ) AS Name,
        COALESCE(MAX(CASE WHEN Name = 'A' THEN Value END),
                 MAX(CASE WHEN Name = 'B' THEN Value END)
                ) AS Value
     FROM TheTable t1
     GROUP BY 1;

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference