根据SQL中给定的详细信息创建“树状明智报告". [英] Tree Wise Report Creation as per the given details in SQL.

问题描述

我有一个表，必须从中创建指定格式的报告.这是我们必须从中创建表的表字段.

用户名	UserID	CountyGroupname	项目名称	工作类型	持续时间
ABC	123	CountyGroup 1	项目1	工作类型1	12
ABC	123	CountyGroup 2	项目2	工作类型3	18

在此表中，我们可以输入N条记录，但就目前而言，我给出的示例数据很少.每个CountygGroupName可以具有n个项目，并且每个项目都在其下定义了很少的工作类型.

输出报告应如下所示:-

UserName	UserID	CountyGroup 1	CountyGroup 2	CountyGroup 3
		Project1 ----- N县1组下的项目数	Project1- ---- N县2组下的项目数	项目1 ----- N县3组下的项目数	总计
		工作类型1 ----项目下的工作类型N	工作类型1 ----项目下的工作类型N	工作类型1 ----项目下的工作类型N
ABC	123	12	18		30

她可以看到，在第一行中，我们将所有不同的CountyGroupname定义为列，而在这些列之下，则将位于该县下的所有项目创建为列，并且在每个项目下，将每个项目下的工作类型定义为列.

请帮助我并指导我，我该如何为CountyGroupName创建动态列，并根据CountyGroupName在其下创建项目，最后在Project name下创建Worktype作为列，然后我可以根据该字段填充适当的工期计数. ="h2_lin">解决方案

使用PIVOT-UNPIVOT

使用PIVOT和UNPIVOT [在SQL Server中透视数据 [UsernameUserIDCountyGroupnameProjectnameWorktypeDurationABC123CountyGroup 1Project 1Worktype 112ABC123CountyGroup 2Project 2Worktype 318
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.

解决方案

Use PIVOT - UNPIVOT

Using PIVOT and UNPIVOT[^]
Pivoting data in SQL Server[^](Alternate ways)

这篇关于根据SQL中给定的详细信息创建“树状明智报告".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！