Pivot All Columns

Question

Post reply

Pivot All Columns

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
November 20, 2013 at 10:14 am

#395866

Hello Everyone
I am playing around with Pivot. But I am not sure if this particular instance is a Pivot or not. I have some test tables setup with only one row in each. Make things simple when learning.
With the data below, I would like to see the resultset align in a vertical fashion like it is listed below.
IF OBJECT_ID('TempDB..#Member','U') IS NOT NULL
DROP TABLE #Member
CREATE TABLE TempDB..#Member
(
RowID int IDENTITY(1,1) PRIMARY KEY NOT NULL
, FirstName varchar(25)
, MiddleName varchar(25)
, LastName varchar(25)
, StreetAddress varchar(100)
, Suite varchar(5)
, CityName varchar(25)
, StateName varchar(25)
, PostalCode varchar(15)
, EmailAddress varchar(25)
);
INSERT INTO #Member
(
FirstName
, MiddleName
, LastName
, StreetAddress
, Suite
, CityName
, StateName
, PostalCode
, EmailAddress
)
VALUES
(
'Samuel'
, 'Paul'
, 'Jones'
, '123 West Main Street'
, '5'
, 'No Town'
, 'This State'
, '12345'
, 'SamTheFarmer@Gmail.com'
);
select * from
#Member;
This is what I want the resultset to look like
ColumnNames Value
RowID: 1
FirstName: Samuel
MiddleName: Paul
LastName: Jones
StreetAddress: 123 West Main Street
Suite: 5
CityName: No Town
StateName: This State
PostalCode: 12345
EmailAddress: SamTheFarmer@Gmail.com
I am on the correct path to using a Pivot? If so, I cannot seem to make it work. I know that I am close, but I need your assistance
Thank You in advance for your time, assistance, comments and suggestions.
Andrew SQLDBA

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You're looking for UNPIVOT or an alternate version using CROSS APPLY and VALUES[/url].

select x.*

from #Member

CROSS APPLY(VALUES('RowID', CAST( RowID AS varchar(100))),

('FirstName', FirstName),

('MiddleName', MiddleName),

('LastName', LastName),

('StreetAddress', StreetAddress),

('Suite', Suite),

('CityName', CityName),

('StateName', StateName),

('PostalCode', PostalCode),

('EmailAddress', EmailAddress))x(Field, Value);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Thank You Luis

That is perfect, and so much cleaner than the code I had.

Greatly appreciate it.

Andrew SQLDBA

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

Luis

Must all non-text datatypes be converted to varchar()? Things like datetime datatypes.

Thanks

Andrew SQLDBA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You don't have to convert all of them explicitly using CAST or CONVERT unless you need a special format.

As all values will show in a single column, the first value will determine the column data type.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Luis

This is what I have as a float value in the table: 1.19056542300058E+15

And this is what I have after performing the CROSS APPLY: 1.19057e+015

I would like to see them both the same, to both look like the value in the table. How would I perform that? I have tried a couple CAST and CONVERT of float and to float, but nothing is helping.

What would you suggest?

Thanks in advance for your assistance.

Andrew SQLDBA

ScottPletcher SSC Guru Points: 101119 More actions · Answer 6

Luis Cazares (11/20/2013)
As all values will show in a single column, the first value will determine the column data type.

I don't think that's right. I think SQL uses data type precedence to determine the final data type of the column. That is, if you use:

CROSS APPLY (

VALUES(some_varchar_col),(some_int_col),(some_decimal_col)

) ca1(some_col)

The data type of "some_col" will be decimal, even though it is last in the VALUES list.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Unfortunately, with approximate numerics you don't have many options. And here they are:

DECLARE @n float = -1.19056542300058E+15

SELECT @n float,

CONVERT( varchar(13), @n, 0 ) with_6_digits,

CONVERT( varchar(15), @n, 1 ) with_8_digits,

CONVERT( varchar(23), @n, 2 ) with_16_digits

Unless, you get some code to format your number as you wish.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

You're right Scott. I'm not sure why did I have that in mind.

Silly me.:hehe:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

You could maintain some semblence of the meta-date for each "element" by using SQL_Variant for 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)

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 10

Thanks Everyone

That worked really well.

Greatly appreciate it

Andrew SQLDBA