Transpose rows into one column

  • 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!

  • 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

  • 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!

  • Is there anyway to keep the NULL data when UNPIVOT ?

  • 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

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

    I`ll apreciate a little help ๐Ÿ˜€

  • 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

  • --===== 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

  • 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

  • Hmm it was that simple :hehe:

    Thank you very much !

  • 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

  • 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

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

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