SQL Server query for getting single value from each column into a single column

Question

I'll start directly by explaining with an example. Suppose I have a table which has 3 columns as shown.

enter image description here

Now what I am trying to achieve is, I want the first values of each individual column into a single column. So it would be something like this,

enter image description here

I have tried a few queries here including using TOP 1 and other incorrect ways. But I am still missing something here to achieve the exact output.

Need some guidance here on how to achieve this. Thank you.

Here is the link which might help you using `UnPivot` http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows — Mahesh, Jan 20 '15 at 06:36
@KrishKvR - Specific is not a mandatory thing. I would be fine with one row or 500 rows. But I want to get the values of a row in a single column. — Dhrumil, Jan 20 '15 at 06:39

Sarath Subramanian · Accepted Answer · 2015-01-20T07:13:28.553

SAMPLE TABLE

SELECT * INTO #TEMP
FROM
(
    SELECT 1 BATCH_ID,'AAA' ASSIGNMENTTITLE,'FILE' ASSIGNMENTTYPE
    UNION ALL
    SELECT 1,'AAA1','FILE'
    UNION ALL
    SELECT 1,'AAA','FILE'
  )TAB

If you need the second row specifically you can do the below

QUERY

;WITH CTE AS
(
    -- Order row according to default format
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT(0))) RNO,*
    FROM #TEMP
)
SELECT CAST(BATCH_ID AS VARCHAR(20)) FROM CTE WHERE RNO=2
UNION ALL
SELECT ASSIGNMENTTITLE FROM CTE  WHERE RNO=2
UNION ALL
SELECT ASSIGNMENTTYPE FROM CTE  WHERE RNO=2

Click here to view result

UPDATE

Since there are 3 items in each record, it can be puzzled unless and otherwise an a column is for each items in a record.

;WITH CTE AS
(
    -- Order row according to default format
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT(0))) RNO,*
    FROM #TEMP
)
SELECT CAST(BATCH_ID AS VARCHAR(20)),RNO 
FROM CTE 
UNION ALL
SELECT ASSIGNMENTTITLE,RNO  
FROM CTE  
UNION ALL
SELECT ASSIGNMENTTYPE,RNO  
FROM CTE  
ORDER BY RNO

Click here to view result

Well, I just managed to change my requirement. But nevertheless, I will accept this as answer because if I were to do it, this would've helped. — Dhrumil, Jan 20 '15 at 07:42

score 0 · Answer 2 · answered Jan 20 '15 at 06:36

0

You can use the concat() function to create a column consisting of all the desired values

More info here

answered Jan 20 '15 at 06:36

Hemant

1,999
2
12
8

This is not helping. Thanks for the answer though. – Dhrumil Jan 20 '15 at 06:45

score 0 · Answer 3 · answered Jan 20 '15 at 06:43

Simply you can try this. If want specific for a row use rowid. For all columns Use unpivot

create table #temp(id int, name varchar(100), title varchar(100))
insert into #temp values(1,'aaa','file')
insert into #temp values(1,'aaas','filef')
insert into #temp values(1,'aaaww','filefs')

select * from #temp
select top 1 cast(id as varchar) title from #temp
union
select top 1 name from #temp
union
select top 1 title from #temp

drop table #temp

score 0 · Answer 4 · answered Jan 20 '15 at 06:58

0

This might help you

select top 1 convert(varchar(10), batch_id) ASSIGNMENTTITLE from table
union all
select top 1 ASSIGNMENTTITLE  from table
union all
select top 1 ASSIGNMENTTYPE  from table

answered Jan 20 '15 at 06:58

Sravani Annepu

118
1
13

score 0 · Answer 5 · answered Jan 20 '15 at 07:26

If this is really what you want: "I want the first values of each individual column into a single column" it would be:

select ASSIGNMENTTITLE
from (
select min(convert(varchar(10), batch_id)) ASSIGNMENTTITLE, 
1 ColOrder from table
union all
select min(ASSIGNMENTTITLE),
2 ColOrder from table
union all
select min(ASSIGNMENTTYPE), 
3 ColOrder from table
) as data
order by ColOrder

SQL Server query for getting single value from each column into a single column

5 Answers5