Select all cell values of a single row table and list it as separate row in SQL

Question

I've a table like this

ID        col1         col2        col3      col4
---------------------------------------------------
1          a            b           c         d
2          e            f           g         h

So if I pass the ID 2 it should return all the colum values as separate rows as this

Colum     Value
---------------------
ID        2
Col1      e
col2      f
col3      g
col4      h

So all the cells of that single rows been splitted as separate rows.

How can I accomplish this

score 1 · Answer 1 · answered Jun 07 '17 at 10:42

1

One way to do it with unpivot and union all.

select 'id' as colu,cast(id as varchar(255)) as val from t where id=2
union all
select colu,val
from t
unpivot (val for colu in (col1,col2,col3,col4)) u
where id=2

answered Jun 07 '17 at 10:42

Vamsi Prabhala

48,685
4
36
58

Any possibility of wiithout specifying the column names. I mean all collumns – Sandeep Thomas Jun 07 '17 at 11:26

score 0 · Answer 2 · answered Jun 07 '17 at 10:53

0

You can use cross apply as below:

Select Id as [Column], [Value]  from yourcols
    cross apply (values(col1), (col2), (col3), (col4)) rws([Value])
    where Id = 2

answered Jun 07 '17 at 10:53

Kannan Kandasamy

13,405
3
25
38

Any possibility of wiithout specifying the column names. I mean all collumns – Sandeep Thomas Jun 07 '17 at 11:26
I dont think so... Without mentioning columnname how engine know what data you need to unpivot... that you can generate dynamically using information_schema or sys.columns rite? – Kannan Kandasamy Jun 07 '17 at 11:36

score -1 · Answer 3 · answered Jun 07 '17 at 10:39

-1

Please refer the below stack over flow link SQL query to split column data into rows

found the question identical as yours.

answered Jun 07 '17 at 10:39

vijay anirudh

31
2

Hope you go through that question.. Its actually splitting the second cell data into rows – Sandeep Thomas Jun 07 '17 at 11:31

Select all cell values of a single row table and list it as separate row in SQL

3 Answers3