Tree Wise Report Creation as per the given details in SQL.

See more:

SQL-Server

I have a table and from which i have to create a report in a specified format.Here is the table fields from which we have to create the report.

Username	UserID	CountyGroupname	Projectname	Worktype	Duration
ABC	123	CountyGroup 1	Project 1	Worktype 1	12
ABC	123	CountyGroup 2	Project 2	Worktype 3	18

In this table we can have N number of records enter but for time been i had given few sample data.Every CountygGroupName can have n number of Projects and every projects have few Worktypes defined under in it.

The Output report should be look like this:-

UserName	UserID	CountyGroup 1	CountyGroup 2	CountyGroup 3
		Project1-----N Number of project under CountyGroup 1	Project1-----N Number of project under CountyGroup 2	Project1-----N Number of project under CountyGroup 3	Total
		Workytype 1----Worktype N under Projects	Workytype 1----Worktype N under Projects	Workytype 1----Worktype N under Projects
ABC	123	12	18		30

Her we can see that in the first row we have all the distinct CountyGroupname Defined as columns and below those columns we have all the projects which lies under that county is created as columns and under each project the worktype under each project is defined as columns.

Please help me and guide me that how i approach to create such Dynamic Column for CountyGroupName and under it Project as per the CountyGroupName and finally Worktype as column under Project name and then i can fill the appropriate Duration Count as per the field.