Getting different column values into one single column

Question

enter image description hereI have 5 columns and i want to get values from those 5 columns and put them into a single column.

+---------------+----------+----------+----------+---------------+
| Option_1      | Option_2 | Option_3 | Option_4 | Option_5      |
+---------------+----------+----------+----------+---------------+
| Below average | Average  | Good     | Better   | Above average |
+---------------+----------+----------+----------+---------------+

So in output i want Below Average, Average, Good, Better and above Average in one column Named Option

Possible duplicate of [SQL Server : Columns to Rows](https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — maSTAShuFu, Feb 20 '18 at 03:43

Laxmi · Accepted Answer · 2018-02-20T05:30:01.870

0

Union all can be done:

SELECT
  Option1 AS Options
FROM (SELECT
  option1
FROM tablename
UNION ALL
SELECT
  option2
FROM tablename
UNION ALL
SELECT
  option3
FROM tablename
UNION ALL
SELECT
  option4
FROM tablename
UNION ALL
SELECT
  option5
FROM tablename) tablename;

or

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.

SELECT
  opt.Options
FROM (SELECT
  *
FROM yourtable) t
UNPIVOT (Options FOR tables IN ([Option_1], [Option_2], [Option_3], [Option_4], [Option_5])) AS opt
ORDER BY tables;

For more info please visit: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

edited Feb 20 '18 at 05:30

answered Feb 20 '18 at 03:24

Laxmi

3,561
21
30

I think i failed to make you understand my problem @Prabhath. I want Below average, Average, Good, Better, Above average in one column and in different rows. – akshay sahu Feb 20 '18 at 03:42
I want output like. **Option** Below Average(next line) Average(next line) Good(next line) Better(next line) Above Average(next line) I dont know how to make them into next line in comments – akshay sahu Feb 20 '18 at 03:44
@Laxmi I'm unware about pivot and unpivot but as much as i searched on internet i got to know that one of these will work for me. I tried to implement but no success. – akshay sahu Feb 20 '18 at 03:50
please refer the image i have linked for output. Thanks a lot for helping me but your query doesnt giving the desired output :/ – akshay sahu Feb 20 '18 at 05:22
:( @akshaysahu sorry...!! but i got the output – Laxmi Feb 20 '18 at 05:31

Prabhath Amaradasa · Answer 2 · 2018-02-20T14:16:46.403

SELECT CONCAT(Option_1,', ',Option_2,', ',Option_3,' ,',Option_4,',',Option_5) as Option
FROM TAble1

if you want results as a one column but multiple rows

SELECT Option_1 AS Option     FROM Table1
UNION ALL
SELECT Option_2  AS Option  FROM  Table1
UNION ALL  
SELECT Option_3 AS Option  FROM Table1
UNION ALL  
SELECT Option_4 AS Option  FROM Table1
UNION ALL  
SELECT Option_5  AS Option  FROM Table1

Using UNPIVOT

SELECT
  rslt.Options
FROM (SELECT
  *
FROM Table1 ) AS dt
UNPIVOT (Options FOR tables IN ([Option_1], [Option_2], [Option_3], [Option_4], [Option_5])) AS rslt

Sample Link

score 0 · Answer 3 · answered Feb 20 '18 at 04:32

declare @table table( Option_1 varchar(50),Option_2 varchar(50), Option_3 varchar(50), Option_4 varchar(50), Option_5  varchar(50))

insert into @table
select  'Below average', 'Average', 'Good','Better','Above average'

select p.typename,p.numericvalue from (
select * 
from @table
) a
unpivot(numericvalue for typename in([Option_1] ,[Option_2] , [Option_3] , [Option_4] , [Option_5]  )) as p
order by typename

output

typename    numericvalue
Option_1    Below average
Option_2    Average
Option_3    Good
Option_4    Better
Option_5    Above average

Sreenu131 · Answer 4 · 2018-02-20T05:36:14.763

Sample Data

IF OBJECT_ID('dbo.Sampletable')IS NOT NULL
DROP TABLE Sampletable
GO
CREATE TABLE Sampletable (
      Option_1 varchar(50)
     ,Option_2 varchar(50)
     ,Option_3 varchar(50)
     ,Option_4 varchar(50)
     ,Option_5  varchar(50)
     )

INSERT INTO Sampletable
SELECT  'Below average', 'Average', 'Good','Better','Above average'

Using Dynamic Sql and Cross apply we get the expected result

DECLARE @DynamicCol nvarchar(max),@Sql nvarchar(max)

SELECT @DynamicCol=
STUFF((SELECT ', '+'('+''''+COLUMN_NAME+''''+','+ COLUMN_NAME +')'FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Sampletable'
FOR XML PATH ('')),1,1,'')

SET @Sql='
SELECT   Typename
        ,Numericvalue 
FROM Sampletable t
CROSS APPLY (VALUES '+@DynamicCol+
    ') AS dt (Typename,Numericvalue )'
PRINT @Sql
EXEC (@Sql)

Sample demo Result

Typename    Numericvalue
-------------------------
Option_1    Below average
Option_2    Average
Option_3    Good
Option_4    Better
Option_5    Above average

Getting different column values into one single column

4 Answers4