Another COLUMNS TO ROWS (or vice-versa) question

Question

Post reply

Another COLUMNS TO ROWS (or vice-versa) question

kappa

SSC Veteran

Points: 259
More actions
June 4, 2008 at 6:26 am

#72168

Hi everybody,
My first post here, so be gentle, OK? 😉
My table:
ID Name Phone
-------- -------------- -------------------
1 John 1234567
2 Peter 9876543
Should look like this:
Row1 Row2
---------- ----------
1 2
John Peter
1234567 9876543
Question 1: Is there any way to accomplish this, short and simple SQL code (known number of rows and columns)?
Question 2: Is there any way to accomplish this in form of a stored procedure (dynamically changing numbers of rows & columns)?
Thanx.

Viewing 6 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 1

kappa (6/4/2008)
No?
Gee, and I thought this was an easy one... :unsure:

Ok... I'll try to be gentle even with an attitude like that... 😉

You haven't provided enough information... why do you want to do this, for example? Correct answer shouldn't include anything like "I was told to"... no, we need the business reason so we can give you the best help possible. The big reason for that is that this is not the way a database should be used. Also, what is the maximum number of rows that you want to pivot?

The other thing is that there has to be at least a hundred good posts about how to do this... have you considered a search? 😛

--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)

kappa SSC Veteran Points: 259 More actions · Answer 2

Hi, thanks for getting involved and sorry about the attitude, not enough sleep, I guess 🙂

OK, here we go. I have done the search, and then some... Among loads of goodies that came accross, I found out about those UNION, PIVOT, UNPIVOT and what-not approaches used to take care of similar requests (cross-tabbing).

But, none of the posts seemed to apply on FULL Rows to Columns "conversion" problem. Oh, and for the reason "why", I cannot provide a better answer than - I got this as some kind of a test question. Sorry.

The thing is, I could find my way out by using a temp table, cursor and some complex CASE mess, which got me to the current point (details in my other post http://www.sqlservercentral.com/Forums/Topic511782-8-1.aspx). But, what I'm after would be a (parametrized) view. Is it possible?

Oh, and for the end:

Create table Test (TestID int, Name varchar(50), Phone varchar(20))

Insert into Test values(1, 'John', '1234567')

Insert into Test values(2, 'Peter', '9876543')

Cheers.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Considering it's a test - you're going to need to do most of the work.....

That being said - you're going to want to look up the SQL 2000 "method" for unpivoting. the UNPIVOT syntax is much too unforgiving for what you're looking for. And ultimately - you're going to find that the "full table" vs not doesn't really matter in your scenario. You'll be able to recognize it - it involves a lot of CASE and UNION ALL statements.

Finally - remembering that "physical order" has no meaning in a SQL table, unless you add an attribute marker as one of your columns, doing this kind of unpivot will "wreck" your output (since you can't count on physical order - you'll have no way to know which new row used to be your NAME column).

Posting what you come up with (even unsuccessfully) will likely get you some comments or pointers.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

kappa SSC Veteran Points: 259 More actions · Answer 4

kappa

SSC Veteran

Points: 259

June 5, 2008 at 9:31 am

#824896

Cool, thanks. Will do.

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

kappa (6/5/2008)
Hi, thanks for getting involved and sorry about the attitude, not enough sleep, I guess 🙂
OK, here we go. I have done the search, and then some... Among loads of goodies that came accross, I found out about those UNION, PIVOT, UNPIVOT and what-not approaches used to take care of similar requests (cross-tabbing).
But, none of the posts seemed to apply on FULL Rows to Columns "conversion" problem. Oh, and for the reason "why", I cannot provide a better answer than - I got this as some kind of a test question. Sorry.
The thing is, I could find my way out by using a temp table, cursor and some complex CASE mess, which got me to the current point (details in my other post http://www.sqlservercentral.com/Forums/Topic511782-8-1.aspx). But, what I'm after would be a (parametrized) view. Is it possible?
Oh, and for the end:
Create table Test (TestID int, Name varchar(50), Phone varchar(20))
Insert into Test values(1, 'John', '1234567')
Insert into Test values(2, 'Peter', '9876543')
Cheers.

I don't believe a parameterized view would be possible for this... especially since views don't take parameters.

The problem I asked about remains... what do you want to do if you have data that is more than two rows long?

--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)