如何以自定义格式显示sql表 [英] how to display sql table in custom format

问题描述

嗨

我有这种格式的sql表

离开代码假名假名员工名部门

  ML Medical  10  sanjay编程

  CL Casual  5  sanjay编程

  ML Medical  5  Chandru Marketing

  CL Casual  6  Chandru Marketing

我需要使用gridview或任何其他控件以这种格式显示

员工名称ML CL部门

 Sanjay  10   5 编程

 Chandru  5   6 市场营销

解决方案

选中此链接

http://www.sqllion.com/2009/05 /group-by-clause-sql-server-2005-vs-2008/ [ http://www.java2s.com/Code/Oracle/Result-Set/AUNIONquerythatusesanORDERBYclause.htm [ ^ ]

和使用分组依据

以下查询可以在ms access中工作

 选择 EmployeeName sum(ML) as  ML，Sum(CL) as  CL，从
选择 EmployeeName，请假 as  ML， 0   as  CL，从 from  tableName
联盟
选择 EmployeeName， 0   as  ML，请假 as  CL，从 from  tableName出发
grup 由 EmployeeName，DepartMent 

请使用以下存储过程，您可以按照自己的智慧进行操作

  SELECT 员工姓名，[ML]，[CL]，部门
 FROM 
(选择
员工姓名，
离开代码，
部门，
休假数
 FROM 
员工
组  BY 员工姓名，离开代码，部门，离开人数
)数据表
枢
(
MAX(剩余人数)
 FOR 离开代码
 IN 
(
[ML]，[CL]
)
数据透视表

问候，
荒谬

hi

I have a sql table in this format

leavecode  leavename       leavecount      employeename      department

  ML           Medical         10            sanjay             programming

  CL           Casual          5             sanjay             programming

  ML           Medical         5             Chandru            Marketing

  CL           Casual          6             Chandru            Marketing

I need to display in this format using gridview or any other control

employeename        ML         CL        department

 Sanjay             10         5         programming

 Chandru            5          6         Marketing

解决方案

check this link

http://www.sqllion.com/2009/05/group-by-clause-sql-server-2005-vs-2008/[^]

It Will Help You

use Union Query to do it
check folling link :


http://www.java2s.com/Code/Oracle/Result-Set/AUNIONquerythatusesanORDERBYclause.htm[^]

and Use Group By

following Query can work in ms access

Select EmployeeName  sum(ML) as ML,Sum(CL) as CL ,DepartMent  from (
Select EmployeeName , LeaveCount as ML , 0 as CL , DepartMent from tableName
Union
Select EmployeeName , 0 as ML , LeaveCount as CL , DepartMent from tableName)
grup by EmployeeName ,DepartMent

Hi,
Please use follow stored procedure, you can get as per your wise

SELECT Employeename,[ML],[CL],Department
FROM 
	(SELECT
		Employeename,
		Leavecode,		
		Department,
		Leavecount		
	FROM	
		Employee
	GROUP BY Employeename,Leavecode,Department,Leavecount	
	) DataTable
PIVOT
(	
	MAX(Leavecount)
	FOR Leavecode
	IN
	(
		[ML],[CL]
	)	
)PivotTable

Regards,
anbukrish

这篇关于如何以自定义格式显示sql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！