SQL 2005中的ROWS的COlumns [英] COlumns to ROWS in SQL 2005
本文介绍了SQL 2005中的ROWS的COlumns的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2张表格,内容如下:
UserTable
Id Name Place
----------- ------------------------
1 Vidya德里
2 Minu Banglore
详细信息表
ID SPEC资格
-------------------------------------
1嗨+2
1 Hello Degree
实际上我想要以下格式输出
UserID名称地方规格资格ED1 ED2
----------- --------------- --------------------------------------------
1 Vidya Delhi Hi +2 Hello Degree
请用查询更新
Thanx提前
Saju A
解决方案
阅读关于dy的文章namic 转动 [ ^ ]。
我发现不是确切的解决方案,但我希望它会有所帮助。
SELECT [ID] AS [UserID],[Name],[Place],
STUFF(( SELECT ' ,' + [Spec] AS ' text()'
FROM @ detailtable t2
WHERE t2。[ID ] = t1。[ID]
F.或 XML PATH(' ')), 1 , 1 ,' ') AS [Specs],
STUFF(( SELECT ' ,' + [Qualification] AS ' text()'
FROM @ detailtable t2
WHERE t2。[ID] = t1。[ID]
FOR XML PATH(' ')),< span class =code-digit> 1 , 1 ,' ') AS [资格]
FROM @ usertable t1
GROUP BY [ID],[姓名],[地点]
GO
结果:
UserID 名称 地点 <第>功能次>资格次> 1 维蒂娅 的Delphi 您好,Hello +2,Degree 2 Minu Banglore NULL NULL
I have 2 table with the following details.
UserTable
Id Name Place
----------- ------------------------
1 Vidya Delhi
2 Minu Banglore
Detail Table
ID SPEC Qualification
-------------------------------------
1 Hi +2
1 Hello Degree
Actually i want the out put in below format
UserID Name Place Spec Qualification ED1 ED2
----------- -----------------------------------------------------------
1 Vidya Delhi Hi +2 Hello Degree
Please update with the query
Thanx in advance
Saju A
解决方案
Read my article about dynamic pivotting[^].
I''ve found not exact solution, but i hope it would be helpful.
SELECT [ID] AS [UserID], [Name], [Place], STUFF( (SELECT', ' + [Spec] AS 'text()' FROM @detailtable t2 WHERE t2.[ID] = t1.[ID] FOR XML PATH('')), 1, 1, '') AS [Specs], STUFF( (SELECT', ' + [Qualification] AS 'text()' FROM @detailtable t2 WHERE t2.[ID] = t1.[ID] FOR XML PATH('')), 1, 1, '') AS [Qualifications] FROM @usertable t1 GROUP BY [ID], [Name], [Place] GO
Result:
UserID Name Place Specs Qualifications 1 Vidya Delphi Hi, Hello +2, Degree 2 Minu Banglore NULL NULL
这篇关于SQL 2005中的ROWS的COlumns的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文