locked
Select multiple column into a single column RRS feed

  • Question

  • I'm trying to select col1 as 'newCol', col2 as 'newCol' , col3 as 'newCol'  from Tablename

    so that i will have all records in col1, col2, col3 all in 'newCol'

    Thanks

    Tuesday, July 30, 2013 4:04 PM

Answers

  • Hi Try This,

    Select Col1 as COL from YourTable
    UNION ALL
    Select Col2 as COL from YourTable
    UNION ALL
    Select Col3 as COL from YourTable
    /*Make Sure COl1,col2 and col3 Should have same Datatype*/

    Thanks
    Rihan

    • Marked as answer by Dayo.O Wednesday, July 31, 2013 12:53 PM
    Wednesday, July 31, 2013 10:16 AM

All replies

  • If all your columns are string type, you can concatenate them with + sign:

    SELECT col1 + col2 +col3 as newCol  FROM Tablename

    If you have other data type, you may need to convert/CAST them to string type before you can concatenate them.

    • Proposed as answer by Sarat (SS) Tuesday, July 30, 2013 5:18 PM
    Tuesday, July 30, 2013 4:25 PM
  • Try this
    SELECT isnull(col1,'') + isnull(col2 ,'')+ isnull(col3,'') as newCol  FROM Tablename


    Satheesh

    Tuesday, July 30, 2013 5:01 PM
  • If you use SQL Sever 2012, you can use the new CONCAT function:

    select concat(col1,col2,col3)  as newCol From SS2012DBTable

    Tuesday, July 30, 2013 6:10 PM
  • No, i'm not trying to concatenate. What i want is if col1 contains 3 records, col2 contains 4 records and col3 contains 6 records then newCol will have 13 records in it.

    Thanks

    Wednesday, July 31, 2013 9:37 AM
  • Hi Try This,

    Select Col1 as COL from YourTable
    UNION ALL
    Select Col2 as COL from YourTable
    UNION ALL
    Select Col3 as COL from YourTable
    /*Make Sure COl1,col2 and col3 Should have same Datatype*/

    Thanks
    Rihan

    • Marked as answer by Dayo.O Wednesday, July 31, 2013 12:53 PM
    Wednesday, July 31, 2013 10:16 AM
  • This makes no sense. Rows are not records; columns contain scalar values. Have y9u ever read a book on RDBMS, so you know the basic terms? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 31, 2013 2:54 PM