SQL 2005中的ROWS的COlumns [英] COlumns to ROWS in SQL 2005

问题描述

我有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屋！