如何在SQL Server中将列值显示为行

问题描述

大家好，

我在表emp中有数据，为

用户名部门
Mohd.wasif网站
Mohd.wasif Sigma
mohd.wasif SQL Server
Sumit.singh C#
Sumit.singh Oracle
Sumit.singh Java

现在，我选择UserName mohd.wasif，然后输出应为

Hi All,

I have data in a table emp as

UserName Department
Mohd.wasif Web
Mohd.wasif Sigma
mohd.wasif SQL Server
Sumit.singh C#
Sumit.singh Oracle
Sumit.singh Java

Now I I select UserName mohd.wasif then output should be

UserName      Department
mohd.wasif   Web,Sigma,SQL Server

之后，我想将此查询的输出与另一个表作为具有UserName列的Register联接起来.

请帮助

there after I want to join the output from this query with another table as Register having column UserName .

Please Help

推荐答案

SELECT username as Name,
           MAX( CASE seq WHEN 1 THEN department ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 2 THEN department ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 3 THEN department ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 4 THEN department ELSE '' END ) as Department
      FROM ( SELECT p1.username, p1.department ,
                    ( SELECT COUNT(*)
                        FROM emp p2
                        WHERE p2.username = p1.username
                        AND p2.department <= p1.department )
             FROM emp p1 ) D ( username, department, seq )
     where username='Mohd.wasif' GROUP BY username ;

谢谢

Thanks

BEGIN
DECLARE @DepartmentNames VARCHAR(1000)

DECLARE @UserName AS VARCHAR(20)

SET @UserName = 'Mohd.wasif'

    SELECT @DepartmentNames = COALESCE(@DepartmentNames,'') + Department+ ','
    FROM Emp WHERE UserName = @UserName

SELECT A.* FROM
    (SELECT @UserName AS UserName, @DepartmentNames AS Dept) AS A
--INNER JOIN Register AS B
--ON A.UserName = B.UserName

END

这篇关于如何在SQL Server中将列值显示为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！