Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Question

Post reply

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Viewing 15 posts - 91 through 105 (of 243 total)

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

Pam Loree-Garcia Newbie Points: 1 More actions · Answer 1

Great article. Thank you. You saved me from the evils of trying to use Pivot.

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

Thanks, Pam... I appreciate your feedback. And, cool Avatar! Welcome to SQLServerCentral.com! 🙂

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

thivyapraba Valued Member Points: 62 More actions · Answer 3

Jeff,

Nice article, But how do you measure the performance, is there any tools?

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

thivyapraba (9/10/2008)
Jeff,
Nice article, But how do you measure the performance, is there any tools?

There's lot's of tools like Performance Monitor, etc. But, I didn't use anything quite so sophisticated for this article. If you look at the code in the section of the article called "Performance", you'll find judicial use of the SET STATISTICS IO and SET STATISTICS TIME commands... for non-looping queries, they tell you a lot about the performance of the code.

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

Nice Artilce Jeff Moden.

I dont know if you posted Dynamic PIVOT article but here is mine :).

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

Thanks, Madhivanan, my old friend. No, haven't written Part II to this article, yet. Got caught up in a new job and can hardly keep up with new posts.

I saw your article before... found it when I started doing research for Part II. Good stuff! Thanks for posting the link here. If it's ok, I'd like to include it in the article for Part II when I finally get to it. Would you mind?

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

Jeff Moden (9/17/2008)
Thanks, Madhivanan, my old friend. No, haven't written Part II to this article, yet. Got caught up in a new job and can hardly keep up with new posts.
I saw your article before... found it when I started doing research for Part II. Good stuff! Thanks for posting the link here. If it's ok, I'd like to include it in the article for Part II when I finally get to it. Would you mind?

Jeff Moden, you can very well include it. I would never mind 🙂

Madhivanan

Failing to plan is Planning to fail

JMI SSChampion Points: 11831 More actions · Answer 8

I have a very clear understanding of how to do a Cross-Tab now.

How about Uncross-Tab?

Does anyone have any samples?

Thanks,

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

riga1966 (10/9/2008)
I have a very clear understanding of how to do a Cross-Tab now.
How about Uncross-Tab?
Does anyone have any samples?
Thanks,

Heh... that's called either "UnPivot" or "Normalizing". You could probably do a search for "Unpivot" or "Converting Columns to Rows" and find a good amount of information on the subject.

And, thank you for the nice feedback, Riga. 🙂

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

jon.malenfant Grasshopper Points: 19 More actions · Answer 10

Taking this to the next level, let's look at textual data instead of numeric. Consider two tables with a one to many relationship. We can even remove the first from the mix because that is a final join to get the name field. The key is the second table has the ID, the property name column, and the value for that property. Property names are different and not pre-definable.

CREATE TABLE #People (PersonID int, PersonName varchar(50))

CREATE TABLE #PeopleProps (PersonID int, PropertyName varchar(50), PropertyValue varchar(50))

Insert Into #People (PersonID , PersonName)

Select 1, 'BillyBob' Union All

Select 2, 'Jethrow'

Insert Into #PeopleProps (PersonID, PropertyName, PropertyValue)

Select 1, 'Height', '6 feet' Union All

Select 1, 'Attitude', 'Poor' Union All

Select 2, 'Height', '5 feet' Union All

Select 2, 'Attitude', 'Ok' Union All

Select 2, 'Talent', 'Banjo'

The desired output:

PersonName Height Attitude Talent

-------------------------------------------

BillyBob 6 feet Poor

Jethrow 5 feet Ok Banjo

Since we can't take advantage of computation to build the secondary columns, how can we go about generating this output? And again, I gave the first table for clarity. It can be removed from the equation for clarity of the solution.

Thanks very much.

Jon

Charles Kincaid SSChampion Points: 13593 More actions · Answer 11

Jon,

I'm not sure how you would go about that. Your PersonProps table will get you flamed, but good, by many folks on this forum. It's called an Entity-Attribute-Value (EAV) design. It's practically impossible to meaningful queries as you have seen.

I still use EAV tables for some very limited things. Application settings for one thing. I never report on them. Used to use it for security permissions but then found intersection tables better for that. The table contains a user id and a permission id for anything that is granted.

ATBCharles Kincaid

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 12

Taking the basic idea posted by Chris Morris (page 2 of this thread) and modifying the string build part gets the following. If there are duplicate properties for a person this displays the maximum (alphabetically).

DECLARE @sql VARCHAR(4000)

SELECT @sql = 'SELECT p.PersonName, ' + STUFF(sep, LEN(sep), 1, '') +

' FROM #People p JOIN #PeopleProps pp ON p.PersonID = pp.PersonID GROUP BY p.PersonName'

FROM (

SELECT 'MAX(CASE PropertyName WHEN ''' + PropertyName + ''' THEN PropertyValue ELSE '''' END) AS [' + PropertyName + '],'

FROM (SELECT PropertyName FROM #PeopleProps GROUP BY PropertyName) x

FOR XML PATH('')

) AS z(sep)

EXEC (@sql)

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 13

Charles Kincaid (10/14/2008)
It's practically impossible to meaningful queries as you have seen.

But, "practically" and "is" are two different things... 😉

--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: 1004290 More actions · Answer 14

jon.malenfant (10/14/2008)
Taking this to the next level, let's look at textual data instead of numeric. Consider two tables with a one to many relationship. We can even remove the first from the mix because that is a final join to get the name field. The key is the second table has the ID, the property name column, and the value for that property. Property names are different and not pre-definable.
CREATE TABLE #People (PersonID int, PersonName varchar(50))
CREATE TABLE #PeopleProps (PersonID int, PropertyName varchar(50), PropertyValue varchar(50))
Insert Into #People (PersonID , PersonName)
Select 1, 'BillyBob' Union All
Select 2, 'Jethrow'
Insert Into #PeopleProps (PersonID, PropertyName, PropertyValue)
Select 1, 'Height', '6 feet' Union All
Select 1, 'Attitude', 'Poor' Union All
Select 2, 'Height', '5 feet' Union All
Select 2, 'Attitude', 'Ok' Union All
Select 2, 'Talent', 'Banjo'
The desired output:
PersonName Height Attitude Talent
-------------------------------------------
BillyBob 6 feet Poor
Jethrow 5 feet Ok Banjo
Since we can't take advantage of computation to build the secondary columns, how can we go about generating this output? And again, I gave the first table for clarity. It can be removed from the equation for clarity of the solution.
Thanks very much.
Jon

Like this except for one thing... you have to predefine the column names... heh... at least until I get my finger out of my nose and write the next article on cross-tabs... 😛

SELECT PersonName,

MAX(CASE WHEN PropertyName = 'Height' THEN PropertyValue END) AS Height,

MAX(CASE WHEN PropertyName = 'Attitude' THEN PropertyValue END) AS Attitude,

MAX(CASE WHEN PropertyName = 'Talent' THEN PropertyValue END) AS Talent

FROM (--==== Derived table "d" relates the people and properties as a "pre-aggregate" just to simplify the whole problem...

SELECT p.PersonID,

p.PersonName,

pr.PropertyName,

pr.PropertyValue

FROM #People p

INNER JOIN #PeopleProps pr

ON p.PersonID = pr.PersonID) d

GROUP BY PersonID,PersonName

--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: 1004290 More actions · Answer 15

Dang... I gotta learn to read all the new posts before I reply...

Richard Fryar's fine solution is a dynamic solution that will work fine in SQL Server 2005.

Heh... if you guys keep it up, I won't have to write the article. 🙂

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