如何将n列连接成一列？

我的目标是如果我有这个：

colmuns      c1 | c2 | c3 | c4 | c5 | n..
row1          a |  a |  a |  a |  a | 
row2          b |  b |  b |  b |  b |
rowN...

我想做一个会返回的查询

   myCol
   aaaaa
   bbbbb
   nnnnn...

我知道我能做到

select t2.id, (
    select  *
    from mytable t1
    where t1.id= t2.id
    for xml path('')) as row
from mytable t2

它会将具有许多列的整行放入一列中，就像我想要的那样

现在，如何过滤掉xml标签呢？

或者还有其他解决方案吗？

edit列可能为 null，不是 varchar，可以是 int、varchar、date 等

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

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

如果某些列可能包含空值，您可以使用ISNULL() 函数, as in

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

You can 动态创建这样的 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 ...