How to fetch rows by applying multiple filtres to a single column in sql server 2008?

Question

I have the following table in my sql server 2008 database:

MsgID    TrackerId    MsgContent

  1        123           red    //MsgContent corresponding to colour
  2        123          E2120   //MsgContent corresponding to model
  5        123           sam    //MsgContent corresponding to password
  1        111          orange  //MsgContent corresponding to colour
  2        111          M3420   //MsgContent corresponding to model
  5        111           pam    //MsgContent corresponding to password

I want a single query whose result is as follows:

TrackerId     Colour    Model    Password   

  123          red      E2120       sam
  111          orange   M3420       pam 

So, how should I go about solving this problem? Thanks in advance.

Answer 1

Here's a version using PIVOT. My only problem with this is the unnecessary aggregate function. I don't know your table definition, but if you have only the columns MsgID, TrackerID, MsgContent, then the CTE that selects the grouping, spreading, and aggregation columns to pivot is superfluous. If you do have more columns, then keep the CTE, otherwise you will get null values in your results.

SELECT TrackerID, [1] [Colour], [2] [Model], [5] [Password]
FROM 
(
  SELECT 
    MsgID, -- spreading column
    TrackerID, -- grouping column
    MsgContent -- aggregation column
  FROM Trackers
) p
PIVOT
(
  MAX(MsgContent)
  FOR MsgID IN( [1], [2], [5] )
) AS pvt

SQLFiddle

You can also use a select for each type of value.

SELECT DISTINCT TrackerID,
  (SELECT MsgContent FROM trackers t2 
   WHERE t2.MsgID = 1 AND t2.TrackerID = t1.TrackerID) [Colour],
  (SELECT MsgContent FROM trackers t2 
   WHERE t2.MsgID = 2 AND t2.TrackerID = t1.TrackerID) [Model],
  (SELECT MsgContent FROM trackers t2 
   WHERE t2.MsgID = 5 AND t2.TrackerID = t1.TrackerID) [Password]
FROM Trackers t1

SQLFiddle