MERGE to target columns using source rows?

Question

I have some nicely-structured data that looks like this:

CREATE TABLE SourceBodyPartColors
(
 person_ID INTEGER NOT NULL, 
 body_part_name VARCHAR(5) NOT NULL
    CHECK (body_part_name IN ('hair', 'eye', 'teeth')), 
 color VARCHAR(20) NOT NULL, 
 UNIQUE (color, body_part_name, person_ID)
);

INSERT INTO SourceBodyPartColors (person_ID, body_part_name, color)
   VALUES (1, 'eye', 'blue'), 
          (1, 'hair', 'blond'), 
          (1, 'teeth', 'white'), 
          (2, 'hair', 'white'), 
          (2, 'teeth', 'yellow'), 
          (3, 'hair', 'red');

Sadly, the target structure is no so nice, and looks more like this:

CREATE TABLE TargetBodyPartColors
(
 person_ID INTEGER NOT NULL UNIQUE, 
 eye_color VARCHAR(20), 
 hair_color VARCHAR(20), 
 teeth_color VARCHAR(20)
);

INSERT INTO TargetBodyPartColors (person_ID)
   VALUES (1), (2), (3);

I can write a SQL-92 UPDATE like this:

UPDATE TargetBodyPartColors
   SET eye_color = (
                    SELECT S1.color
                      FROM SourceBodyPartColors AS S1
                     WHERE S1.person_ID 
                              = TargetBodyPartColors.person_ID
                           AND S1.body_part_name = 'eye'
                   ), 
       hair_color = (
                     SELECT S1.color
                       FROM SourceBodyPartColors AS S1
                      WHERE S1.person_ID 
                               = TargetBodyPartColors.person_ID
                            AND S1.body_part_name = 'hair'
                    ), 
       teeth_color = (
                      SELECT S1.color
                        FROM SourceBodyPartColors AS S1
                       WHERE S1.person_ID 
                                = TargetBodyPartColors.person_ID
                             AND S1.body_part_name = 'teeth'
                     );

...but the repeated code bothers me.

A good canidate for simplifying using MERGE, I thought, but I can't come up with anything reasonable.

Any ideas how to use MERGE with this data. (Note: I want to avoid the proprietary UPDATE..FROM syntax, thanks.)

@marc_s: I can see why you might think that it's EAV but really it's not. See the Wikipedia article about First Normal Form (http://en.wikipedia.org/wiki/First_normal_form): the 'Target' structure is like the `Customer` table shown under the subheading 'Repeating groups across columns' (i.e. 1NF violation). The 'Source' structure is more like the `Customer Telephone Table` table shown under the 'A design that complies with 1NF' subheading. — onedaywhen, Dec 02 '10 at 15:55
@onedaywhen - I disagree with that. My rule of thumb for 1NF violation is can you swap the column values and still end up with something semantically the same. If you can swap `tel1` and `tel2` and the semantics are unchanged this is clearly not in 1NF. c.f. [Facts and Fallacies about First Normal Form](http://www.simple-talk.com/sql/learn-sql-server/facts-and-fallacies-about-first-normal-form/) — Martin Smith, Dec 02 '10 at 16:01
@Martin: you have your own personal definition of 1NF?! Well, erm, ... I guess that must be comforting for you :) — onedaywhen, Dec 02 '10 at 16:04
@Martin: Seriously, though. I recognize that my quickly drafted data has flaws e.g. 'hair color' is not the same domain as 'eye color'... who has blond eyes?! :) — onedaywhen, Dec 02 '10 at 16:07
@Martin: I'm familiar with the article you link to, which IMO is subjective in places. I tend towards Date's version of 1NF myself. Hopefully this doesn't affect my chances of getting a practicable answer :) — onedaywhen, Dec 02 '10 at 16:09
@onedaywhen AFAIK you already have the only practicable answer. It is not possible to update the same row more than once using `Merge` as it raises Error #8672 so the only way this can work is to pivot the source. Obviously you can use the old style `CASE` and `GROUP BY` to do the Pivoting if you don't want to use `PIVOT`. — Martin Smith, Dec 02 '10 at 16:23

score 6 · Accepted Answer · answered Dec 02 '10 at 15:40

WITH Pivoted AS
(
  SELECT person_ID, eye, hair, teeth
  FROM SourceBodyPartColors
    PIVOT
    (
    MAX (color) FOR body_part_name IN ( [eye], [hair], [teeth] )
    ) AS pvt
  )  
MERGE TargetBodyPartColors AS target
USING  Pivoted AS source
ON (target.person_ID = source.person_ID)
WHEN MATCHED THEN 
UPDATE SET eye_color = source.eye,  
           hair_color = source.hair,  
           teeth_color = source.teeth ;

MERGE to target columns using source rows?

1 Answers1

Linked