SQL Server query : rows make columns (Pivot?)

I hava a problme to make a query.

This query which I created

WITH TATH(Priority, EntryDate) AS 
(
    SELECT TH.Priority as Priority, DATEADD(dd, 0, DATEDIFF(dd, 0, entryDate)) as EntryDate  
      FROM TicketAssignment TA, TicketHeader TH 
     WHERE TA.TicketID = TH.TicketID   
       AND TA.Company = 'IT'
       AND TA.CurrentRole IN ('SA1B','SA1C','SDA')
) 
SELECT Priority, convert(varchar(10), EntryDate,103) as EntryDate, COUNT(*) AS Count  
FROM TATH 
GROUP BY Priority, EntryDate

and the result so far is:

Priority    EntryDate   Count
0   25/11/2011  1
1   25/11/2011  2
2   25/11/2011  36
3   25/11/2011  8
0   28/11/2011  3
1   28/11/2011  3
2   28/11/2011  37
3   28/11/2011  37

I want to make the result look like this

EntryDate   Priorty0    Priority1   Priority2   Priority3
25/11/2011  1   2   36  8
28/11/2011  3   3   37  37

I think it is out of my ability I look up pivot but I cannot achieve it.

If a pivot is not a good solution, what I have to search (or study)?

Could you help me Q.Q?

Pivoting is much like grouping. You could view it as limited grouping with a ‘special effect’. The limitation consists in the fact that there can only be just one aggregated column. (In normal GROUP BY query you can have more than one, naturally.) And by the ‘special effect’ I mean, of course, that one of the other columns (and, again, only one) gets transformed into multiple columns.

Let's take your GROUP BY query as an example. You've got three columns in the output. One of them, Count, is the very column that contains aggregated information. That is the one that would be scattered among multiple columns in a PIVOT query. Another column, Priority, is one of the two other columns the results are grouped by and also the one that needs to be pivoted. Finally, EntryDate is the other GROUP BY column. It should simply stay as it is, because it does not directly take part in pivoting.

Let's see now how your main SELECT gets transformed from a usual GROUP BY query into a PIVOT query, step by step:

Since grouping is implied in a PIVOT query, the GROUP BY clause is removed. Instead, a PIVOT clause is introduced.
The Count column's expression is moved from the SELECT clause to the PIVOT clause.
The splitting of the Priority column is defined in the PIVOT clause.
The Priority and Count columns in the SELECT clause are replaced by the list of the columns defined in the PIVOT clause.
The EntryDate column stays unchanged in the SELECT clause.

And here's the resulting query, with comments marking every part of the transformation described above:

WITH TATH(Priority, EntryDate) AS 
(
    SELECT TH.Priority as Priority, DATEADD(dd, 0, DATEDIFF(dd, 0, entryDate)) as EntryDate
      FROM TicketAssignment TA, TicketHeader TH 
     WHERE TA.TicketID = TH.TicketID   
       AND TA.Company = 'IT'
       AND TA.CurrentRole IN ('SA1B','SA1C','SDA')
) 
SELECT
  convert(varchar(10), EntryDate,103) as EntryDate,                       -- #5
  [0] AS Priority0, [1] AS Priority1, [2] AS Priority2, [3] AS Priority3  -- #4
FROM TATH
PIVOT (                                                                   -- #1
  COUNT(*)                                                                -- #2
  FOR Priority IN ([0], [1], [2], [3])                                    -- #3
) p

/*  -- your original main query, for comparison
SELECT
  Priority,                                                               -- #4
  convert(varchar(10),                                                    -- #5
  EntryDate,103) as EntryDate, COUNT(*) AS Count                          -- ##2&4
FROM TATH 
GROUP BY Priority, EntryDate                                              -- #1
*/

There's one additional note on the column list in the PIVOT clause. First of all, you must understand that the resulting set of a SQL query is supposed to be fixed^* in terms of the number of columns and their names. That means you must explicitly enumerate all the transformed columns you want to see in the output. The names are derived from the values of the column being pivoted but they should be specified as names, not as values. That is why you can see square brackets around the listed numbers. Since numbers themselves do not satisfy the rules for regular identifiers, they must be delimited.

You can also see that you can alias pivoted columns in the SELECT clause just like any other column or expression. So, in the end, you don't have to end up with the meaningless 0, 1 etc. identifiers and instead you can assign those columns any names you like.

_{^*If you want the number and/or names of the pivoted columns to be dynamic, you'll have to build the query dynamically, i.e. collect the names first, then incorporate them into a string containing the rest of the query and invoke the final query with EXEC () or EXEC sp_executesql. You can search this site for more information on dynamic pivoting.}