On Aug 23, 4:26*pm, Dinesh <dinesh...@gmail.comwrote:
Quote:
Originally Posted by
Hi experts,
>
I am working on SQL Server 2005.
Now i have to write a query which will extract some information from a
table.
My main table is having few columns supose 3 columns.
>
EmpID *Supervisor_ID * *Date_Of_Visit
>
Now records are entering in this tabl. Date of visit is date of
supervisor vist on which employee supervisor visited his work.
Generally its on monthly basis. A new row is entered in the table on
every visit. But in some cases employee may be visited more than once
may be two times or 10 times depending on the situation, *and for each
visit one new row is inserted in the table.
>
Now i have to extract the information from last one yearppose I am
running this query today In August 2008 *then it should show the
employee id and his date of visit from September 2007 to August 2008.
All 12 date of visits should appear in row format.
There will be 13 columns in the result.
One for EmpID and rest 12 for date of visits Starting from DOV1, Dov2
till DOV12. And if there will be more than onve Date_Of_Visit for any
employee then it should come in the same month column seprated by
comma. And if there is no visit in any month then that columns should
appear with null value
>
for ex
>
EmpID *Supervisor_ID * *Date_Of_Visit
1 * * * 300 * * * * * * 20/08/2007
1 * * * 300 * * * * * * 25/08/2007
1 * * * 300 * * * * * * 29/08/2007
2 * * * 250 * * * * * * 21/08/2007
1 * * * 300 * * * * * * 21/09/2007
2 * * * 250 * * * * * * 21/09/2007
1 * * * 300 * * * * * * 21/10/2007
2 * * * 250 * * * * * * 21/10/2007
1 * * * 300 * * * * * * 21/11/2007
2 * * * 250 * * * * * * 21/11/2007
1 * * * 300 * * * * * * 21/12/2007
2 * * * 250 * * * * * * 21/12/2007
1 * * * 300 * * * * * * 21/01/2008
2 * * * 250 * * * * * * 21/01/2008
1 * * * 300 * * * * * * 21/02/2008
2 * * * 250 * * * * * * 21/02/2008
2 * * * 250 * * * * * * 21/05/2008
1 * * * 300 * * * * * * 02/08/2008
1 * * * 300 * * * * * * 11/08/2008
2 * * * 250 * * * * * * 08/08/2008
2 * * * 250 * * * * * * 19/08/2008
>
Now i have to write a query which will give result like below
>
EmpID * * * * * * * DOV1 * * * * * * * * DOV2 * * * DOV3
DOV4 * * *DOV5 * * * DOV6 * * * *DOV7 * * * *DOV8 *DOV9 DOV10
DOV11 * * * DOV12
>
1 * * *20/08/2007,25/08/2007,29/08/2007 21/09/2007 21/10/2007
21/11/2007 21/12/2007 *21/01/2008 *21/02/2008
02/08/2008,11/08/2008
>
2 * * * * * * * *21/08/2007 * * * * * * 21/09/2007 21/10/2007
21/11/2007 21/12/2007 *21/01/2008 *21/02/2008
08/08/2008,19/08/2008
>
How i can get this result, Please help me.
>
Any help wil be appriciated.
>
Regardstabse
Dinesh
Dear Sir,
Thanks for the early reply.
I am sneding you the my create table and insert statements for your
convenience. I can not see the Northwind data base so some difficulty
to understand the logic, becuase i am not aware of that databse. given
below is the Create table and insert statment
CREATE TABLE [dbo].[Superviser_Visit](
[EmpID] [int] NOT NULL,
[Supervisor_ID] [int] NOT NULL,
[Date_Of_Visit] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
)
INSERT INTO Superviser_Visit VALUES (1, 300, 20/08/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 25/08/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 29/08/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/08/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/09/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/09/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/10/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/10/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/11/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/11/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/12/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/12/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/01/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/01/2008)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/02/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/02/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/05/2008)
INSERT INTO Superviser_Visit VALUES (1, 300, 02/08/2008)
INSERT INTO Superviser_Visit VALUES (1, 300, 11/08/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 08/08/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 19/08/2008)
So if possible please give some idea to me now.
Thanks for the help.
Regards
Dinesh
