SQL single column rows into multiple columns

Question

Post reply

SQL single column rows into multiple columns

Johnny D

Ten Centuries

Points: 1030
More actions
January 17, 2014 at 5:59 pm

#303419

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.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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)

RKaram SSCommitted Points: 1518 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)

Johnny D Ten Centuries Points: 1030 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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)

Johnny D Ten Centuries Points: 1030 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

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)

Johnny D Ten Centuries Points: 1030 More actions · Answer 11

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

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)