SQL Query multiple values of a column as multiple columns (SQL 2005)

When I run a SQL query on a single table and here is the data (this is just a sample, error column might be more than 10)

time   total  Error  

00:16    6    10000(E)

00:20    4    10000(E)

00:46    2    10000(E)

01:01    2    100开发者_开发问答00(E)

01:40    2    10000(E)

02:07    2    10000(E)

02:52    1    10000(E)

04:27    2    10000(E)

04:29    6    10000(E)

04:32    4    10000(E)

04:49    2    10000(E)

04:50    2    10000(E)

06:18    2    10000(E)

09:04    1    10000(E)

10:57    4    10000(E)

10:58    4    10000(E)

00:36    1    9401(E)

00:37    1    9401(E)

00:57    1    9401(E)

00:58    1    9401(E)

01:32    1    9401(E)

01:33    1    9401(E)

02:36    2    9401(E)

03:05    1    9401(E)

03:06    1    9401(E)

09:53    2    9401(E)

12:11    2    9401(E)

12:12    4    9401(E)

12:41    1    9401(E)

I want to write a SQL query so that I want to get the above data like this

time    10000(E)    9401(E)
---------------------------

00:16    6            0

00:20    4            0

00:36    0            1

00:37    0            1

00:46    2            0

00:57    0            1

00:58    0            1

01:01    2            0

01:32    0            1

01:33    0            1

01:40    2            0

02:07    2            0

02:36    0            2

02:52    1            0

03:05    0            1

03:06    0            1

04:27    2            0

04:29    6            0

04:32    4            0

04:49    2            0

04:50    2            0

06:18    2            0

09:04    1            0

09:53    0            1

10:57    4            0

10:58    4            0

12:11    0            2

12:12    0            4

12:41    0            1

is this possible??

---

Does this meet your requirement?

select e.time
        , e.[10000(E)]
        , e.[9401(E)]
    from (
        select time
                , SUM(case when Error LIKE N'10000(E)' then Total else NULL end) as [10000(E)]
                , null as [9401(E)]
            from MyTable
            where Error LIKE N'10000(E)'
            group by time
        union
        select time
                , null as [10000(E)]
                , SUM(case when Error LIKE N'9401' then Total else NULL end) as [9401(E)]
            from MyTable
            where Error LIKE N'9401(E)'
            group by time
    ) e
order by e.time

If no, please tell me about the result so that I can bring the righteous corrections.

The SUM function only comes to group the number of occurences of a same error into one given time, which seems to be what you have in your table, actually. So, it shouldn't modify any data. On the other hand, if you had two different records of the same error by the same time, then they should be grouped by this time and the total of occurences of this error will be additioned.

---

For your given in- and output it could be as simple as this.

SELECT  *
FROM    (
          SELECT time
                 , [10000(E)] = Total
                 , [9401(E)] = 0
          FROM   YourTable
          WHERE  Error = '10000(E)'
          UNION ALL
          SELECT time
                 , [10000(E)] = 0
                 , [9401(E)] = Total
          FROM   YourTable
          WHERE  Error = '9401(E)'
        ) q
ORDER BY
        time