How to display all the record by date wise?

Question

Using SQL Server 2000

Table

PersonID Date 

001 11-02-2009
002 11-02-2009
003 11-02-2009
001 12-02-2009
004 12-02-2009
005 12-02-2009
003 13-02-2009
005 13-02-2009

So on…,

I want to display all the Personid by Date wise.

Expected Output

PersonID Date

001 11-02-2009
002 11-02-2009
003 11-02-2009
004 11-02-2009
005 11-02-2009
001 12-02-2009
002 12-02-2009
003 12-02-2009
004 12-02-2009
005 12-02-2009
001 13-02-2009
002 13-02-2009
003 13-02-2009
004 13-02-2009
005 13-02-2009

So on…,

All the Personid should appear by date wise.

I wrote a query in Access

Query

SELECT AllPossibleCardEvents.PersonId FROM ((SELECT p.PersonId, AllDates.CardEventDate FROM (SELECT DISTINCT Date FROM TMP_Cardevent2) AllDates, Tmp_cardevent1  p) AllPossibleCardEvents LEFT OUTER JOIN TMP_cardevent2  Actual ON AllPossibleCardEvents.PersonId = Actual.PersonId AND AllPossibleCardEvents.Date = Actual.Date) )

Above Access Query is Working Fine. But the same result I want to show in SQL also.

How to write a query in SQL?

Need Query Help.

Answer 1

If I understand this correctly you want every distinct personid returned for every distinct date.

If so the query would be

SELECT DISTINCT PersonTable.PersonId, DateTable.Date FROM Table as PersonTable
CROSS JOIN (SELECT DISTINCT Date FROM Table) AS DateTable

Answer 2

SELECT A.PersonID, B.Date
FROM (SELECT DISTINCT PersonID from myTable) A, 
(SELECT DISTINCT Date from myTable) B

I have not tried this. But, it will do a cartesian product.