如何将n列连接成一列?

尝试:

;with XmlValues  as
(
    select t2.id, (
        select  *
        from mytable  t1
        where t1.id= t2.id
        for xml path(''), TYPE) as row
    from mytable  t2
)
select x.row.value('.', 'VARCHAR(8000)') as readable
    FROM XmlValues AS x

编辑 工作样本:

DECLARE @YourTable table (c1 int, c2 int, c3 varchar(5), c4 datetime)
INSERT INTO @YourTable VALUES (1,2,'abcde','1/1/2009')
INSERT INTO @YourTable VALUES (100,200,'zzz','12/31/2009 23:59:59')

    select t2.c1, (
        select  *
        from @YourTable  t1
        where t1.c1= t2.c1
        for xml path(''), TYPE) as row
    from @YourTable  t2

;with XmlValues  as
(
    select t2.c1, (
        select  *
        from @YourTable  t1
        where t1.c1= t2.c1
        for xml path(''), TYPE) as row
    from @YourTable  t2
)
select x.c1,x.row.value('.', 'VARCHAR(8000)') as readable
    FROM XmlValues AS x

输出:

c1          row
----------- --------------------------------------------------------------------
1           <c1>1</c1><c2>2</c2><c3>abcde</c3><c4>2009-01-01T00:00:00</c4>
100         <c1>100</c1><c2>200</c2><c3>zzz</c3><c4>2009-12-31T23:59:59</c4>

(2 row(s) affected)

c1          readable
----------- ----------------------------------
1           12abcde2009-01-01T00:00:00
100         100200zzz2009-12-31T23:59:59

(2 row(s) affected)

编辑 从元数据表解析表列名的无循环方式,能够根据需要格式化每个数据类型并支持空值:

BEGIN TRY 
CREATE TABLE YourTable (c1 int, c2 int, c3 varchar(5), c4 datetime) 
INSERT INTO YourTable VALUES (1,2,'abcde','1/1/2009')
INSERT INTO YourTable VALUES (100,200,'zzz','12/31/2009 23:59:59')
end try begin catch end catch

DECLARE @YourTableName   varchar(1000)
DECLARE @YourColumns     varchar(max)
DECLARE @YourQuery       varchar(max)
SET @YourTableName='YourTable'
SELECT
    @YourColumns=STUFF(
                       (SELECT
                            '+ '
                            --' ' --any constant string to appear between columns
                            + CASE DATA_TYPE
                                  WHEN 'datetime' THEN 'COALESCE(CONVERT(char(23),'+CONVERT(varchar(max),COLUMN_NAME)+',121),''NULL'')'
                                  --more datatypes here
                                  ELSE 'COALESCE(CONVERT(varchar(max),' + CONVERT(varchar(max),COLUMN_NAME)+'),''NULL'')'
                              END
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE table_name = @YourTableName
                            FOR XML PATH('')
                       ), 1, 2, ''
                      )

SET @YourQuery  = 'SELECT '+@YourColumns+' FROM '+@YourTableName
PRINT @YourQuery  
SELECT * FROM YourTable

EXEC (@YourQuery)

输出:

SELECT COALESCE(CONVERT(varchar(max),c1),'NULL')+ COALESCE(CONVERT(varchar(max),c2),'NULL')+ COALESCE(CONVERT(varchar(max),c3),'NULL')+ COALESCE(CONVERT(char(23),c4,121),'NULL') FROM YourTable
c1          c2          c3    c4
----------- ----------- ----- -----------------------
1           2           abcde 2009-01-01 00:00:00.000
100         200         zzz   2009-12-31 23:59:59.000

(2 row(s) affected)


------------------------------------------
12abcde2009-01-01 00:00:00.000
100200zzz2009-12-31 23:59:59.000

(2 row(s) affected)

您可以简单地使用T-SQL的字符串连接运算符“+”

SELECT c1 + c2 + c3 + c4 + c5 + ...
FROM myTable

如果某些列可能包含空值,则可以使用 isNull()函数 ,如

SELECT ISNULL(c1, '') + ISNULL(c2, 'x') + ...  -- note how you can substribute NULLs with any desired value
FROM myTable

你可以 动态创建此类select语句 通过利用SQL Server元数据:

SELECT COLUMN_NAME, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'myTable'
   AND DATA_TYPE IN ('char', 'varchar') -- can further filter out non desired colums
order by ORDINAL_POSITION    -- and also pick a given order

例如

DECLARE @SqlStmt AS VARCHAR(8000)
DECLARE @Ctr AS INT
DECLARE @ColName AS VARCHAR(80)

DECLARE colCursor CURSOR 
   FOR SELECT COLUMN_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_name = 'myTable'
      AND DATA_TYPE IN ('char', 'varchar')
  ORDER BY  ORDINAL_POSITION
  FOR READ ONLY;

OPEN colCursor;

SET @Ctr = 0
SET @SqlStmt = 'SELECT '

FETCH NEXT FROM colCursor INTO @colName;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Ctr > 0
    BEGIN
        SET @SqlStmt = @SqlStmt + ' + '; -- w/o the spaces if size is a pb
    END
    SET @Ctr = @Ctr + 1;
    SET @SqlStmt = @SqlStmt + @ColName;   -- w/ ISNULL if needed...

    FETCH NEXT FROM colCursor INTO @colName;
END;
CLOSE colCursor
DEALLOCATE colCursor

SET @SqlStmt = @SqlStmt + ' FROM ' + 'myTable'
-- Here to add to @SqlStmt (WHERE clause, other columns, other 
-- tables/join whatever...

PRINT @SqlStmt  -- OR EXEC() it ...

如果 列都是已知的:

SELECT c1 + c2 + c3 + c4 + c5 AS cAll

但是,如果您不知道前面的列都是什么,这将不起作用。

换句话说,如果您希望查询这个特定的表,它将起作用,但是如果您希望使用一个通用查询来处理不同的表(不同的列名等),那么您需要修改每个要分析的表的查询。