Pivot, unpivot

Question

Pivot, unpivot

mishka-723908

SSCommitted

Points: 1675
More actions
June 23, 2015 at 5:38 pm

#319395

Hello,
I have a table with 150 columns and about 75 rows. I would like to simply flip the table where the rows become (1st attribute will be new col names) columns and column become rows. so the table will end up with 150 rows and 75 columns. Does anyone have a simple way for doing something like this?
Thanks,
Michael

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

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

Dohsan SSCrazy Points: 2972 More actions · Answer 1

Would you have some example data for this with what your expected output would be?

I thought it was just a standard pivot question until I reread what you were asking. What are you trying to achieve by switching the data round in this way?

drew.allen SSC Guru Points: 76985 More actions · Answer 2

Unless all of your columns are the same datatype, this is a VERY BAD IDEA. In order for this to work, all of your columns in your rows would need to be converted to one datatype, because each row would become a single column which can only have one datatype.

One of the big differences between databases and spreadsheets is that databases care about datatypes. This is why transposing rows and columns is hard in a database, but is easy in spreadsheets.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

It's a bad idea as Drew points out, but it's not difficult to do. It's an unusual requirement and often it's a step in a process which would be difficult to accomplish without transposition.

So the first question is - why do you want to do this?

^{“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

mishka-723908 SSCommitted Points: 1675 More actions · Answer 4

I have this meta data just for research purpose that I received from an external system. It will not be used for any processing only research and it would be easier if it was flipped. All the same data type, varchar.

Thank you for your help.

a4apple SSCrazy Points: 2620 More actions · Answer 5

is it something you want to present in the form of a query or just for research purposes? Can you not use Excel if it's a one time quick thing for pivoting? I am not really sure what exactly you want to achieve, but it may help 🙂

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

mishka-723908 SSCommitted Points: 1675 More actions · Answer 6

Hello,

Thanks for your reply. Its just something I hope to get done with a query that I can reuse.

Thanks