locked
Display Multiple Columns into Single Column in sql server RRS feed

  • Question

  • User86716463 posted

    Hi,

    I wanted to display all the columns into single column as below. Please try other than union ... 

    Query :  select Name,address,City, Country from MyTable1 

    ex:

    Ramesh

    122 Mary Street 

    Newyork

    US

    Thanks,

    Ramesh

     

    Friday, July 1, 2016 1:08 PM

Answers

  • User-157633207 posted
    SELECT client, owner, payer, status
    FROM Main
    WHERE status = 'Active'
    
    client | owner | payer | status
    
    1      |  2    |  3    | Active
    10     |  11   | 12    | Active
    

    What you are referring to is known as an UNPIVOT This takes the columns and converts them to rows

    SELECT value
    FROM
    (
      select cast(client as varchar(10)) client, 
        cast(owner as varchar(10)) owner, 
        cast(payer as varchar(10)) payer, 
        status
      from main
      where status = 'Active'
    ) x
    unpivot
    (
      value 
      for field in (client, owner, payer, status)
    ) u
    Column
    1
    2
    3 
    Active
    10
    11
    12
    Active

    Thanks..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 3, 2016 4:05 PM
  • User1559292362 posted

    Hi n.m.rameshraja,

    I wanted to display all the columns into single column as below. Please try other than union ... 

    Query :  select Name,address,City, Country from MyTable1 

    According to your description, it seems that you want convert columns to rows. K_Pramod provide a good idea, we could use UNPIVOT method to achieve it. please check the following code.

    SELECT colname
    FROM
    (
      select Name, 
        address, 
        City, 
        Country
      from MyTable1
    ) x
    unpivot
    (
      colname 
      for col in (Name, address, City, Country)
    ) u

    For more information, please refer to:

    https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 4, 2016 5:33 AM

All replies

  • User753101303 posted

    Hi,

    Do you mean, something like SELECT Name+'<br/>'+Address+'<br/>'+City etc... from MyTable1 ? Which character to use to go to the next line depends on where you'll show that (HTML ?).

    Also I'm not sure doing that on the SQL side is the best option. It might be better to do that when you are about to render those columns in your UI (so that you can still handle those information as separate columns until the very last moment).

    Friday, July 1, 2016 1:33 PM
  • User-157633207 posted
    SELECT client, owner, payer, status
    FROM Main
    WHERE status = 'Active'
    
    client | owner | payer | status
    
    1      |  2    |  3    | Active
    10     |  11   | 12    | Active
    

    What you are referring to is known as an UNPIVOT This takes the columns and converts them to rows

    SELECT value
    FROM
    (
      select cast(client as varchar(10)) client, 
        cast(owner as varchar(10)) owner, 
        cast(payer as varchar(10)) payer, 
        status
      from main
      where status = 'Active'
    ) x
    unpivot
    (
      value 
      for field in (client, owner, payer, status)
    ) u
    Column
    1
    2
    3 
    Active
    10
    11
    12
    Active

    Thanks..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 3, 2016 4:05 PM
  • User1559292362 posted

    Hi n.m.rameshraja,

    I wanted to display all the columns into single column as below. Please try other than union ... 

    Query :  select Name,address,City, Country from MyTable1 

    According to your description, it seems that you want convert columns to rows. K_Pramod provide a good idea, we could use UNPIVOT method to achieve it. please check the following code.

    SELECT colname
    FROM
    (
      select Name, 
        address, 
        City, 
        Country
      from MyTable1
    ) x
    unpivot
    (
      colname 
      for col in (Name, address, City, Country)
    ) u

    For more information, please refer to:

    https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 4, 2016 5:33 AM