SQL to combine data for single row per object

I know this must be a common problem, but I'm not sure where to go. This occurs a few times inside a big nasty query, so here is the problem simplified. I just want one row per object, but the null on ColorOther gives me two rows.

I can think of a few things to try wrapping a query around this, but there must be something simpler... I'm off to read up on pivots.

Table structure

CREATE TABLE [dbo].[Colors](
[ColorId] [int] IDENTITY(1,1) NOT NULL,
[ColoredObjectId] [int] NOT NULL,
[ColorCode] [int] NOT NULL,
[ColorOther] [nvarchar](50) NULL,
 CONSTRAINT [PK_Colors] PRIMARY KEY CLUSTERED 
([ColorId] ASC)

Source data

ColorId     ColoredObjectId ColorCode   ColorOther
----------- --------------- ----------- ----------
1           1               1           NULL
2           1               2           NULL
3           1               4           purple
4           2               2           NULL
5           2               3           NULL
6           2               4           orange
7           3               1           NULL
8           3               3           NULL
9           3               4           green

Query

SELECT   ColoredObjectId                                         ,
         ColorOther                                              ,
         MAX(CASE WHEN ColorCode = 1 THEN 1 ELSE 0 END) AS Yellow,
         MAX(CASE WHEN ColorCode = 2 THEN 1 ELSE 0 END) AS Red   ,
         MAX(CASE WHEN ColorCode = 3 THEN 1 ELSE 0 END) AS Blue  ,
         MAX(CASE WHEN ColorCode = 4 THEN 1 ELSE 0 END) AS Other
FROM     Colors
GROUP BY ColoredObjectId,
         ColorOther

Output

ColoredObjectId ColorOther Yellow      Red         Blue        Other
--------------- ---------- ----------- ----------- ----------- -----------
1               NULL       1           1           0           0
1               purple     0           0           0           1
2               NULL       0           1           1           0
2               orange     0           0           0           1
3               NULL       1           0           1           0
3               green      0           0           0           1

Desired output

ColoredObjectId ColorOther Yellow      Red         Blue        Other
--------------- ---------- -------开发者_JAVA技巧---- ----------- ----------- -----------
1               purple     1           1           0           1
2               orange     0           1           1           1
3               green      1           0           1           1

---

I think this should return the desired output.

SELECT   ColoredObjectId                                         ,
         MAX(ColorOther) AS ColorOther                           ,
         MAX(CASE WHEN ColorCode = 1 THEN 1 ELSE 0 END) AS Yellow,
         MAX(CASE WHEN ColorCode = 2 THEN 1 ELSE 0 END) AS Red   ,
         MAX(CASE WHEN ColorCode = 3 THEN 1 ELSE 0 END) AS Blue  ,
         MAX(CASE WHEN ColorCode = 4 THEN 1 ELSE 0 END) AS Other
FROM     Colors
GROUP BY ColoredObjectId

---

Try this (I am not at a SQL box, so I can't test it, but it should be pretty close)

SELECT   cc.ColoredObjectId                                         ,
         xx.ColorOther                                              ,
         MAX(CASE WHEN ColorCode = 1 THEN 1 ELSE 0 END) AS Yellow,
         MAX(CASE WHEN ColorCode = 2 THEN 1 ELSE 0 END) AS Red   ,
         MAX(CASE WHEN ColorCode = 3 THEN 1 ELSE 0 END) AS Blue  ,
         MAX(CASE WHEN ColorCode = 4 THEN 1 ELSE 0 END) AS Other
FROM     Colors cc
JOIN     (select distinct ColorObjectID,ColorOther FROM colors 
          where colorOther is not null) xx on xx.colorObjectId=cc.colorobjectID
GROUP BY cc.ColoredObjectId,
         xx.ColorOther