how to concatenate n columns into one?

my goal is if I have this:

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

I want to do a query that would return

   myCol
   aaaaa
   bb开发者_如何转开发bbb
   nnnnn...

I know I can do this

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

and it will put the whole row with many columns into one column like I want

now, how to filter out the xml tag?

or is there any other solution?

edit column might be null are not varchar, could be int, varchar, date, etc

You can simply use T-SQL's string concatenation operator '+'

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

In case some of the columns may contain null values you can use the ISNULL() function, as in

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

You can dynamically create such SELECT statements by tapping into SQL Server metadata:

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

For example

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 ...

try:

;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

EDIT working sample:

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

OUTPUT:

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)

EDIT loop free way to parse table column names from meta data tables, with the ability to format each datatype as desired and supports NULLs:

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)

OUTPUT:

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)

If the columns are all known:

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

However, this won't work if you don't know up front what the columns all are.

In other words, if you want a query for this specific table it will work, but if you want a general query that will work with different tables (different column names, etc) you'd need to modify the query for each table you want to parse.