单行数据透视查询
问题描述：
我从查询返回的一个表看起来像这样:-
EmpName
101
102
103
104
105
106
107
我希望所有数据都这样堆积
101 102 103 104 105 106 107
注意:-"EmpName"下的记录数量不是固定的，因此，如果我们有100条记录，则它将显示输出为最多100列的数据透视.
I have a one table returned from a query that looks something like this:-
EmpName
101
102
103
104
105
106
107
and I want all the Data to stack up like this
101 102 103 104 105 106 107
Note:-The number of records under ''EmpName'' is not fixed so in case we have 100 records then it will show the output as pivot up to 100 Columns.
答
假设这些字段是varchar，则可以编写以下代码
Assuming the fields are varchar, you can write the following code
DECLARE @ListEmp VARCHAR(MAX)
SELECT @ListEmp = COALESCE(@ListEmp+',' ,'') + EmpName FROM table_name
SELECT @ListEmp
如果字段是数字，则将EmpName转换为varchar，然后附加相同的内容.
if the fields are numeric, then convert the EmpName to varchar and then append the same..
DECLARE @EmpName AS TABLE (EmpId Bigint)
DECLARE @Cols AS NVARCHAR(MAX)
INSERT INTO @EmpName
SELECT 101 Union
SELECT 102 Union
SELECT 103 Union
SELECT 104 Union
SELECT 105 Union
SELECT 106 Union
SELECT 107 Union
SELECT 108
SET @Cols =''
SELECT @Cols = @Cols + CAST(EmpId AS NVARCHAR(MAX)) + ' As [' + CAST(EmpId AS NVARCHAR(MAX)) + '],'
FROM @EmpName
SET @Cols = 'SELECT ' + SUBSTRING(@Cols , 1 , LEN(@Cols) - 1 )
EXEC sp_ExecuteSQL @Cols