假设我有代表员工信息的DataTable / SQL Table(MicroSoft SQL)
其中包含名字,姓氏,年龄,公司,经验年限,学位
我想根据名字,姓氏,年龄来组合信息
公司,经验年限,学位必须与相应的单元格相结合
firstname lastname age company yearsofexperience Degree
john muller 21 IBM 4years MBA
jan tonny 22, MSoft 1years MS
martin tata 21 apple 2years PHD
john Muller 21 sony 3years MBA
james muller 21 IBM 4years PHD
jan tonny 22 Telsa 1years BS
martin tata 21 sun 2years MBA
james Muller 21 TCS 3years BS
注意:MS SQL解决方案不是Mysql
请找到我的方法来删除重复的行并使其他数据连接在特定的列中
例如,在上面的示例中,我要合并其他类似3个条目中存在的信息
firstname lastname age company yearsofexperience Degree
john muller 21 IBM,sony, 4years,3years, MBA,MBA
jan tonny 22, MSoft,Telsa 1years,1years MS,BS
martin tata 21 apple,sun 2years,2years PHD,MBA
james muller 21 IBM,TCS 4years,3years PHD,BS
是的,我正在寻找实现此目标的最佳方法
如果将表拆分为2个不同的表,这是一种很好的方法吗?可能基于主键匹配。我们可以接其他条目吗?
请帮我谢谢(+1)
如果您使用的是SQL Server,请尝试以下操作:
SELECT T1.firstname
,t1.lastname
,t1.age
,Company = SubString (( SELECT ', ' + T2.company
FROM table_name as T2
WHERE T1.firstname = T2.firstname
and t1.lastname = t2.lastname
and t1.age = t2.age
FOR XML PATH ( '' ) ), 3, 1000)
,yearsofexperience = SubString (( SELECT ', ' + T2.yearsofexperience
FROM table_name as T2
WHERE T1.firstname = T2.firstname
and t1.lastname = t2.lastname
and t1.age = t2.age
FOR XML PATH ( '' ) ), 3, 1000)
,yearsofexperience = SubString (( SELECT ', ' + T2.Degree
FROM table_name as T2
WHERE T1.firstname = T2.firstname
and t1.lastname = t2.lastname
and t1.age = t2.age
FOR XML PATH ( '' ) ), 3, 1000)
FROM table_name as T1
GROUP BY T1.firstname, t1.lastname ,t1.age
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句