how to concatenate n columns into one?

Question

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

Answer 1

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

Answer 2

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)

Answer 3

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.