请帮忙解决这个SQL Server问题
问题描述:
请帮忙解决这个SQL Server问题。我有一名工作人员以及下面列出的字段(请参见粗体显示员工表)。我想要得到如下所示的结果(请参阅粗体结果)。请帮忙解决这个SQL Server问题
EMPLOYEE表:
Name
Age
Salary
Employee Type
Employed Date
员工数据:
Name Age Salary Employee_Type Date_Hired
Employee 1 24 24,000.00 Staff January 15, 2009
Employee 2 33 32,000.00 Staff February 1, 2005
Employee 3 23 75,000.00 Consultant March 20, 2002
结果:
Employee 1 Employee 2 Employee 3
Age 24 33 23
Salary 24,000.00 32,000.00 75,000.00
Employee_Type Staff Staff Consultant
Date_Hired January 15, 2009 February 1, 2005 March 20, 2002
答
Create Table #Employee (Name Varchar(20), Age TinyInt, Salary SmallMoney, EmployeeType Varchar(20), EmployedDate Date)
Insert Into #Employee
Select 'Employee 1', 24, 24000.00, 'Staff', '01/15/2009' Union all
Select 'Employee 2', 33, 32000.00, 'Staff', '02/01/2005' Union All
Select 'Employee 3', 23, 75000.00, 'Consultant', '03/20/2002'
Declare @Names As Varchar(Max), @strSQL Varchar(Max)
Select @Names = Stuff((Select ',' + QuoteName(Name) From #Employee Order by Name For XML Path('')), 1, 1, '')
Set @strSQL = ';With CTE As (Select Name, FieldName, FieldValue From (Select Name, Convert(Varchar(20), Age) As Age, Convert(Varchar(20), Salary) As Salary, Convert(Varchar(20), EmployeeType) As EmployeeType, Convert(Varchar(20), EmployedDate, 101) As EmployedDate From #Employee)As p UnPivot (FieldValue For FieldName In (Age, Salary, EmployeeType, EmployedDate))AS unpvt) Select * From CTE Pivot (Max(FieldValue) For Name in (' + @Names + ')) As Pvt'
print @strSQL
Execute (@strSQL)
Drop Table #Employee
感谢Arbi Baghdanian,:)
+0
高度赞赏任何其他解决方案。:) – user335160
答
这是一种方法。 UNPIVOT用于将列名转移到行,然后通过ColumnName(现在是一行)的标准分组用于为每个员工转换名称/值组合。请注意,在使用unpivot时,列应该具有相同的数据类型,这就是为什么存在转换和转换的原因。这种方法唯一的缺点是员工姓名是硬编码的,但这总是一个问题,除非使用动态透视。但是,既然你提到你只有约10名员工来corsstab,那应该不成问题。这里是脚本:
select
unp.ColumnName,
max(case when Name = 'Employee 1' then Value else null end) [Employee 1],
max(case when Name = 'Employee 2' then Value else null end) [Employee 2],
max(case when Name = 'Employee 3' then Value else null end) [Employee 3]
from
(
select
Name, cast(Age as varchar(20)) Age,
cast(Salary as varchar(20)) Salary,
cast(Employee_Type as varchar(20)) Employee_Type,
convert(varchar(20), Employed_Date, 106) Employed_Date
from dbo.Employee
) src
unpivot
(
Value for ColumnName in (
[Age], [Salary], [Employee_Type], [Employed_Date])
) unp
group by unp.ColumnName;
-- the above produces something like this for
-- the data consistent with the sample in question:
ColumnName Employee 1 Employee 2 Employee 3
-------------------- -------------------- -------------------- -----------
Age 24 33 23
Employed_Date 15 Jan 2009 01 Feb 2005 20 Mar 2002
Employee_Type Staff Staff Consultant
Salary 24000.00 32000.00 75000.00
感谢奥列格。 :)
答
Declare @strSQL1 Varchar(Max)
Declare @Names1 As Varchar(Max)
Select @Names1 = Stuff((Select ',' + 'max(case when Name = '''+Name+'''then Value else null end)'+ QuoteName(Name) From Employee Order by Name For XML Path('')), 1, 1, '')
print @Names1
Set @strSQL1 = 'select
unp.ColumnName,'[email protected]+ ' from
(
select
Name, cast(Age as varchar(20)) Age,
cast(Salary as varchar(20)) Salary,
cast(Employee_Type as varchar(20)) Employee_Type,
convert(varchar(20), Date_Hired, 106) Date_Hired
from dbo.Employee
) src
unpivot
(
Value for ColumnName in (
[Age], [Salary], [Employee_Type], Date_Hired)
) unp
group by unp.ColumnName; '
print @strSQL1
Execute (@strSQL1)
另一种动态的解决方案升级奥列格的解决方案
这是可以做到,但你添加员工添加列和SQL是不适合,没有动态SQL。就个人而言,我会在报告或客户端代码或Access/Excel枢纽中执行此操作 – gbn
gbn-仅作为示例。它不会为员工带来数百条记录。它最多只有10条记录。我在这个问题中将我的原始表格替换为Employee,以获得清晰的视角。 ;) – user335160
@Ocaso Protal-如果我把它放在这里,你会解决它吗?我试图把这个问题只是为了得到一些想法/解决这个问题或另一种方法来解决这个问题。 – user335160