Transpose rows into one column

Question

Transpose rows into one column

MariusC

SSC-Addicted

Points: 452
More actions
July 7, 2014 at 2:56 am

#287580

Hi guys,
I have a table with a lot of data like in the example:
unic | nrc | nr_tel | id_stud
-----+-----+--------+---------
2343 | 123 | 354354 | 123
1231 | 432 | 534523 | 324
It has 30k rows like this.
I want to transpose each row into one column. The output should be like:
data
-----+
2343
123
354354
123
1231
432
534523
324
How can I do this ?
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Take a look at UNPIVOT[/url].

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MariusC SSC-Addicted Points: 452 More actions · Answer 2

I solved it. Yes, the unpivot was the answer. I used the following query :

select Field from myTable UNPIVOT (Field for ColumnName IN ([unic],[nrc],[nr_tel],[id_stud])) unpvt

Thanks!

MariusC SSC-Addicted Points: 452 More actions · Answer 3

Is there anyway to keep the NULL data when UNPIVOT ?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

http://stackoverflow.com/questions/1002989/sql-server-include-null-using-unpivot

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MariusC SSC-Addicted Points: 452 More actions · Answer 5

I have just read that and tried some queries but I can`t obtain the right result.

I`ll apreciate a little help 😀

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

MariusC (7/8/2014)
I have just read that and tried some queries but I can`t obtain the right result.
I`ll apreciate a little help 😀

How about some sample data and desired output?

Check the first link in my signature on how to do this.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MariusC SSC-Addicted Points: 452 More actions · Answer 7

--===== Create the test table with

create table #test

(

id_respondent integer,

projid integer,

gfkroid integer

)

--===== Insert the test data into the test table

insert into #test (id_respondent,gfkroid) values (1234,4567),(2345,4546),(3255,2345)

How the data looks like:

id_respondet | projid | gfkroid

-------------+--------+---------+

1234 | NULL | 4567

2345 | NULL | 4546

3255 | NULL | 2345

How is the desire output:

New_row |

--------+

1234

NULL

4567

2345

NULL

4546

3255

NULL

2345

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

SELECT NewColumn

FROM #test t

CROSS APPLY (VALUES (id_respondent), (projid), (gfkroid)) d (NewColumn)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MariusC SSC-Addicted Points: 452 More actions · Answer 9

Hmm it was that simple :hehe:

Thank you very much !

prabhu.st SSCrazy Points: 2552 More actions · Answer 10

yes, ChrisM's query is simple aswell it is more efficient and Optimized too..

--Estimated Subtree Cost = 0.0034222 (to calculate 21.6 rows)

select Field from #tempunpivot UNPIVOT (Field for ColumnName IN ([unic],[nrc],[nr_tel],[id_stud])) unpvt

--Estimated Subtree Cost = 0.0034131 (to Calculate 24 rows)

SELECT NewColumn

FROM #tempunpivot t

CROSS APPLY (VALUES (unic), (nrc), (nr_tel), (id_stud)) d (NewColumn)

I would like to stick with "Cross Apply", Query thanks to ChrisM & Mouris..

Regards,

Prabhu

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

prabhu.st (7/8/2014)
yes, ChrisM's query is simple aswell it is more efficient and Optimized too..
--Estimated Subtree Cost = 0.0034222 (to calculate 21.6 rows)
select Field from #tempunpivot UNPIVOT (Field for ColumnName IN ([unic],[nrc],[nr_tel],[id_stud])) unpvt
--Estimated Subtree Cost = 0.0034131 (to Calculate 24 rows)
SELECT NewColumn
FROM #tempunpivot t
CROSS APPLY (VALUES (unic), (nrc), (nr_tel), (id_stud)) d (NewColumn)
I would like to stick with "Cross Apply", Query thanks to ChrisM & Mouris..
Regards,
Prabhu

CROSS APPLY VALUES may or may not be quicker than UNPIVOT. There's usually not much in it - and CAV is usually easier to write and test. Dwain Camps has more details of the two methods in this excellent article[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden