SQL single column rows into multiple columns

  • Hi All,

    I have a table with one column with lots of rows. I would like every five rows of data to turn into five columns.

    So in example if I have:-

    Column1

    birds

    gary

    fish

    silver

    blue

    tank

    Bill

    Shine

    Holly

    Grenada

    I would like this to change to

    column1 column2 column3 column4 column5

    birds gary fish silver blue

    tank Bill Shine Holly Grenada

    Regardless of what the data in column1 is.

    I have been tryinh all sorts of queries but have been really struggling with this one.

    Thanks in advance for any assistance.

  • Post what you have tried so far to solve your problem.

  • Johnny D (1/17/2014)


    Hi All,

    I have a table with one column with lots of rows. I would like every five rows of data to turn into five columns.

    So in example if I have:-

    Column1

    birds

    gary

    fish

    silver

    blue

    tank

    Bill

    Shine

    Holly

    Grenada

    I would like this to change to

    column1 column2 column3 column4 column5

    birds gary fish silver blue

    tank Bill Shine Holly Grenada

    Regardless of what the data in column1 is.

    I have been tryinh all sorts of queries but have been really struggling with this one.

    Thanks in advance for any assistance.

    This is actually pretty easy to do. The trouble with your example is that you have nothing that guarantees the order that you want. Is there another column that you forgot to include that does?

    Also, you're almost brand new to these forums so let me first say "Welcome Aboard". I'd also like to give you some tips for how to get tested coded answers for your code-based question much more quickly. Please see the first link in the "Helpful Links" section of my signature line below for what I'm talking about.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I think we need more clarification about your case

    But i have a solution for you as i understand from your question

    Declare a table and then select your column you want transform it into 'n' columns

    Then use a while loop or a curseur to select your five columns you need to use them with a pivot to be transformed on 5 columns

    I hope that answer will help you dear

    Rabih

    Regards

    rkaram

  • rabih_karam (1/18/2014)


    I think we need more clarification about your case

    But i have a solution for you as i understand from your question

    Declare a table and then select your column you want transform it into 'n' columns

    Then use a while loop or a curseur to select your five columns you need to use them with a pivot to be transformed on 5 columns

    I hope that answer will help you dear

    Rabih

    Regards

    Absolutely do NOT need to use a while loop or cursor to accomplish this task.

    We need more information before we can actually provide a viable answer.

  • Lynn Pettis (1/18/2014)


    rabih_karam (1/18/2014)


    I think we need more clarification about your case

    But i have a solution for you as i understand from your question

    Declare a table and then select your column you want transform it into 'n' columns

    Then use a while loop or a curseur to select your five columns you need to use them with a pivot to be transformed on 5 columns

    I hope that answer will help you dear

    Rabih

    Regards

    Absolutely do NOT need to use a while loop or cursor to accomplish this task.

    We need more information before we can actually provide a viable answer.

    Lynn is correct. There's absolutely no need for a Cursor or While loop for this simple problem. I'm just waiting for some clarification on the sort order and then either I or someone like Lynn will show you how easy this problem is. The solution will blow the doors off of any RBAR method to do this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi All,

    Thank you for your input thus far.

    To tell you what I have tried so far. I have had an attempt using SSIS but as I am new to that SSIS tools I soon gave up and concentrated on a sql solution.So here is what I tried.

    I created a new table with two columns. One being an identity column which increments by 1 for each row. I then inserted all the current column values into this table now giving me a table with two columns.

    I then tried the following script:-

    INSERT INTO dbo.StageTable

    (column1, column2, column3, column4 )

    select firstfield from mytable

    where id=1

    union

    select firstfield from mytable

    where id=2

    union

    select firstfield from mytable

    where id=3

    union

    select firstfield from mytable

    where id=4

    I am matching the ids to the first four rows but from here I started to really struggle as to how I can actually get to the next four rows and insert them into the same dbo.StageTable.

    I thought about the while loop but still could not see a way I could achieve this using that as it would need to insert four fields at a time.

    I have probably confused this even more and my apologies if i have but hopefully someone has had to do something similar and can assist me.

    Thanks

  • Johnny D (1/18/2014)


    Hi All,

    Thank you for your input thus far.

    To tell you what I have tried so far. I have had an attempt using SSIS but as I am new to that SSIS tools I soon gave up and concentrated on a sql solution.So here is what I tried.

    I created a new table with two columns. One being an identity column which increments by 1 for each row. I then inserted all the current column values into this table now giving me a table with two columns.

    I then tried the following script:-

    INSERT INTO dbo.StageTable

    (column1, column2, column3, column4 )

    select firstfield from mytable

    where id=1

    union

    select firstfield from mytable

    where id=2

    union

    select firstfield from mytable

    where id=3

    union

    select firstfield from mytable

    where id=4

    I am matching the ids to the first four rows but from here I started to really struggle as to how I can actually get to the next four rows and insert them into the same dbo.StageTable.

    I thought about the while loop but still could not see a way I could achieve this using that as it would need to insert four fields at a time.

    I have probably confused this even more and my apologies if i have but hopefully someone has had to do something similar and can assist me.

    Thanks

    This isn't really a job for SSIS. T-SQL will do it all for you. Of course, you could incorporate the T-SQL in SSIS as a task.

    The method below uses a CROSS TAB to pull this off and will usually do so faster than it can be done with a Pivot. Please see the following articles on more details about the relatively ancient technique of using CROSS TABs.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    As a bit of a sidebar, I strongly recommend that you take a look at the first link under "Helpful Links" in my signature line below for future posts. It saves a whole lot of time and makes it a whole lot easier for you to get tested, coded, demonstrable solutions.

    Here's the code for the 4 column solution using the data that you original posted. As always, the details are in the embedded comments. Post back if you have any questions.

    --==================================================================================

    -- Create and populate a test table according to the latest information given.

    --==================================================================================

    --===== If it exists, drop the temp table to make reruns easier in SSMS.

    -- This may be commented out for production stored procedures.

    IF OBJECT_ID('tempdb..#PreLoad','U') IS NOT NULL

    DROP TABLE #PreLoad

    ;

    --===== Create the table that you spoke of as having an IDENTITY column.

    -- DO NOTE that the IDENTITY column starts at ZERO!

    CREATE TABLE #PreLoad

    (

    RowNum INT IDENTITY(0,1) PRIMARY KEY CLUSTERED

    ,Column1 VARCHAR(20)

    )

    ;

    --===== Load the preload table using whatever method you decided to use.

    -- In this case, I'm just loading the data from your orginal post.

    INSERT INTO #PreLoad

    (Column1)

    SELECT 'birds' UNION ALL

    SELECT 'gary' UNION ALL

    SELECT 'fish' UNION ALL

    SELECT 'silver' UNION ALL

    SELECT 'blue' UNION ALL

    SELECT 'tank' UNION ALL

    SELECT 'Bill' UNION ALL

    SELECT 'Shine' UNION ALL

    SELECT 'Holly' UNION ALL

    SELECT 'Grenada'

    ;

    --==================================================================================

    -- Do the pivot to the 4 columns you want and store the output in the staging

    -- table you want. I create the staging table on the fly, in this case.

    --==================================================================================

    --===== If it exists, drop the temp table to make reruns easier in SSMS.

    -- This may be commented out for production stored procedures.

    IF OBJECT_ID('tempdb..#Staging','U') IS NOT NULL

    DROP TABLE #Staging

    ;

    --===== Here's where the magic of having the IDENTITY column comes in. This will

    -- blow the doors off of any cursor or while-loop solution you could think of.

    -- It uses simple integer division to control the row each item will appear on

    -- and the remainder (modulus) from integer division to control which column

    -- each item will appear on. This will be incredibly fast even on a million

    -- rows.

    SELECT SortOrder = IDENTITY(INT,1,1)

    ,Column1 = MAX(CASE WHEN RowNum%4 = 0 THEN Column1 ELSE '' END)

    ,Column2 = MAX(CASE WHEN RowNum%4 = 1 THEN Column1 ELSE '' END)

    ,Column3 = MAX(CASE WHEN RowNum%4 = 2 THEN Column1 ELSE '' END)

    ,Column4 = MAX(CASE WHEN RowNum%4 = 3 THEN Column1 ELSE '' END)

    INTO #Staging

    FROM #PreLoad

    GROUP BY RowNum/4

    ORDER BY RowNum/4

    ;

    --===== Display the results.

    -- You could leave off the SortOrder column if needed.

    SELECT SortOrder, Column1, Column2, Column3, Column4

    FROM #Staging

    ORDER BY SortOrder

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks Jeff,

    That is exactly what I needed. I would have never have thought of that approach to solving it. I was trying all kinds of tricks including putting values to different rows with a view to combining them later.

    Thanks for your help. I'll sleep a lot better tonight.

    Thanks everyone else for your time and effort too.

  • Johnny D (1/18/2014)


    Thanks Jeff,

    That is exactly what I needed. I would have never have thought of that approach to solving it. I was trying all kinds of tricks including putting values to different rows with a view to combining them later.

    Thanks for your help. I'll sleep a lot better tonight.

    Thanks everyone else for your time and effort too.

    Thanks for the feedback. That being said, do you understand how the CROSS TAB is actually working to give you the results you asked for?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • rabih_karam (1/18/2014)


    I think we need more clarification about your case

    But i have a solution for you as i understand from your question

    Declare a table and then select your column you want transform it into 'n' columns

    Then use a while loop or a curseur to select your five columns you need to use them with a pivot to be transformed on 5 columns

    I hope that answer will help you dear

    Rabih

    Regards

    Take a look at the code I posted. Do you understand it so that you no longer think you need a cursor for this type of problem? And, no.... I'm not trying to chastise you for your original recommendation to use a cursor. I'm trying to teach a better way and want to give you the opportunity to ask questions about it if you need to.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi Jeff,

    After studying your code and doing a little reading I have full understanding of your code. It's an approach i may not have applied off the top of my head. The data I normally work with is a lot better structured and do not normally have to think outside the box as much. It's just in this case I was given data by someone to work with which is very unstructured and 'almost' unworkable. But thanks to you I have learnt something new.

    Thanks for your time and everyone else who took the time to look and comment. Appreciate it.

  • Johnny D (1/19/2014)


    Hi Jeff,

    After studying your code and doing a little reading I have full understanding of your code. It's an approach i may not have applied off the top of my head. The data I normally work with is a lot better structured and do not normally have to think outside the box as much. It's just in this case I was given data by someone to work with which is very unstructured and 'almost' unworkable. But thanks to you I have learnt something new.

    Thanks for your time and everyone else who took the time to look and comment. Appreciate it.

    Thank you for the feedback. And, well done. A lot of folks just "take the code and run". I like to make sure that someone can support the code when they implement such a thing especially when it uses techniques that are no longer documented in Books Online like they used to be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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