select
    a.SNo,
    a.Date,
    a.Cat as Cat_A,
    a.Value as Value_A,
    b.Cat as Cat_B,
    b.Value as Value_B,
    c.Cat as Cat_C,
    c.Value as Value_C
from MyTable as a
left join MyTable as b 
    on b.Date = a.Date and b.Cat = 'B'
left join MyTable as c
    on c.Date = c.Date and c.Cat = 'C'
where a.Cat = 'A'

select
    a.SNo,
    a.Date,
    a.Cat as Cat_A,
    a.Value as Value_A,
    b.Cat as Cat_B,
    b.Value as Value_B,
    c.Cat as Cat_C,
    c.Value as Value_C
from MyTable as a
left join MyTable as b 
    on b.Date = a.Date and b.Cat = 'B'
left join MyTable as c
    on c.Date = c.Date and c.Cat = 'C'
where a.Cat = 'A'

DECLARE @T TABLE
    (SNo INT, Date DATETIME, CAT CHAR(1), Value INT)

INSERT INTO @T (SNo, Date, Cat, Value)
SELECT 1, '2011-11-01','A',100
UNION ALL SELECT 2, '2011-11-02','A',101
UNION ALL SELECT 3, '2011-11-01','B',102
UNION ALL SELECT 4, '2011-11-02','B',100
UNION ALL SELECT 5, '2011-11-01','C',1088
UNION ALL SELECT 5, '2011-11-02','C',1026

SELECT SNo=ROW_NUMBER() OVER (ORDER BY Date)
    ,Date, Cat_A, Value_A, Cat_B, Value_B, Cat_C, Value_C
FROM (
    SELECT Date
        ,Cat_A='A'
        ,Value_A=MAX(CASE Cat WHEN 'A' THEN Value END)
        ,Cat_B='B'
        ,Value_B=MAX(CASE Cat WHEN 'B' THEN Value END)
        ,Cat_C='C'
        ,Value_C=MAX(CASE Cat WHEN 'C' THEN Value END)
    FROM @T
    GROUP BY Date) a

 SELECT SNo = MIN(SNo)
      , Date
      , Cat_A   = 'A'
      , Value_A = MAX(CASE Cat WHEN 'A' THEN Value END)
      , Cat_B   = 'B'
      , Value_B = MAX(CASE Cat WHEN 'B' THEN Value END)
      , Cat_C   = 'C'
      , Value_C = MAX(CASE Cat WHEN 'C' THEN Value END)
   FROM @T
  GROUP BY Date

 SELECT SNo = MIN(SNo)
      , Date
      , Cat_A   = 'A'
      , Value_A = MAX(CASE Cat WHEN 'A' THEN Value END)
      , Cat_B   = 'B'
      , Value_B = MAX(CASE Cat WHEN 'B' THEN Value END)
      , Cat_C   = 'C'
      , Value_C = MAX(CASE Cat WHEN 'C' THEN Value END)
   FROM @T
  GROUP BY Date

INSERT INTO @T (SNo, Date, Cat, Value)
SELECT 11, '2011-11-01','A',100
UNION ALL SELECT 12, '2011-11-02','A',101
UNION ALL SELECT 13, '2011-11-01','B',102
UNION ALL SELECT 14, '2011-11-02','B',100
UNION ALL SELECT 15, '2011-11-01','C',1088
UNION ALL SELECT 15, '2011-11-02','C',1026