单行数据透视查询 [英] Single row Pivot query

问题描述

我从查询返回的一个表看起来像这样:-

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

这篇关于单行数据透视查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！