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

Question

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

Viewing 15 posts - 151 through 165 (of 243 total)

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

steve dassin SSCrazy Points: 2396 More actions · Answer 1

Jeff Moden (4/17/2009)
steve dassin (4/17/2009)
I agree. Learning something new is just a form of grandstanding. It's a crutch for people who really haven't mastered what their already using 🙂
Then learn something new, Steve. Learn to make a point without displaying a condescending and arrogant attitude and without words that drip with sarcasm. You're a smart man, but no one will listen to you because of your in-your-face lack of manners.

Oh Jeff I don't feel I've really been sarcastic in these forums. That artistry I reserve for Joe Celko and a few others who have passed off the trite as the significant and turned common sense on its head 🙂 But lets put mind games aside and play another type of game. Lets play some psychedelic sql. A kewl type of mind game. One of the most mind altering things in BOL, IMHO, is a simple statement found under table variables:

'table (Transact-SQL)'

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1ef0b60e-a64c-4e97-847b-67930e3973ef.htm

Is a special data type that can be used to store a result set for processing later. table is primarily used is for temporary storage of a set of rows returned as the result set of a table-valued function.

'Assignment operation between table variables is not supported.'

Of the myriad blogs and chatterings about sql have you ever read anything about this simple sentence? To be fair given the biblical amount written in the net communities I've seen nothing about this either (perhaps they can be excused as it doesn't readily lead itself to point and click) 🙂 Now here we're talking about a mind game because here is a case of a variable not being a variable 🙂 Can you imagine if someone wrote this about numbers:

Assignment operation between integer variables is not supported.

Now is MS playing head games here? What kind of variable could it be that it couldn't be assigned to another variable? And this is my segway into what I was attempting to talk about with Sergiy. Though MS uses the term 'variable' in reference to a table it ain't like any variable anyone's ever seen before 🙂 Now lets play what if, what if BOL said just the opposite about table variables:

'Assignment operation between table variables is supported.'

This idea is a trip in new direction. This sentence in BOL is the fork in the road, the dividing line between two completely different interpretations of what a table is. It's the dividing line between sql and a relational system. It raises kewl mind musings. What is the nature of an sql table such that it doesn't support assignment. Just how would a system interpret a table such that it could support it. How would a system recognize such a table? How is sql recognizing a table that is different than the other? Does sql use the concept of 'type' with a table? Is 'type' used differently in a relational system? Finally what if sql tables had a concept of equality like numbers and strings? What does it mean for two tables to be equal and why doesn't sql support it? Food for thought, more thought I hope. And I reserve the right to follow this up 🙂

best,

steve

www.beyondsql.blogspot.com

steve dassin SSCrazy Points: 2396 More actions · Answer 2

Paul White (4/18/2009)
steve dassin (4/17/2009)
I agree. Learning something new is just a form of grandstanding. It's a crutch for people who really haven't mastered what their already using 🙂
Hey Steve,
Just so you know, I found your post to be quite amusing!
Quite clever, even. 🙂
Not seen anything from you previously, so I can't say whether Jeff's comments are fair or unfair; though I tend to attach weight to Jeff's words, so...keeping an open mind.
Learning is good. The day I stop learning, I will know I died during the night.
Paul

Hello Paul,

If you talk a little sql, a little trash and a lot of common sense we'll get along fine 🙂 I'm really a pussycat. I'm also the nail that gets hammered here (only kidding) 🙂 I'm attempting to talk about two different worlds that have traditionally been at odds with each other. No one has had much success but sometimes the dice rolls crazy ways. Hopefully I won't crap out 🙂

>Learning is good. The day I stop learning, I will know I died during the night

High five 🙂

best,

steve

www.beyondsql.blogspot.com

Paul White SSC Guru Points: 150468 More actions · Answer 3

Steve,

We should start a new thread for the discussion of table 'variables'.

I want to respond, but this thread is for discussion of Jeff's article on cross-tabs and pivots.

Cheers,

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

steve dassin (4/18/2009)
Oh Jeff I don't feel I've really been sarcastic in these forums.

That would be part of the problem.

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

Paul White SSC Guru Points: 150468 More actions · Answer 5

Jeff Moden (4/19/2009)
That would be part of the problem.

I quote "you owe me a new monitor!" :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

Paul White (4/18/2009)
Steve,
We should start a new thread for the discussion of table 'variables'.
I want to respond, but this thread is for discussion of Jeff's article on cross-tabs and pivots.
Cheers,
Paul

Thanks, Paul.

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

Paul White (4/19/2009)
Jeff Moden (4/19/2009)
That would be part of the problem.
I quote "you owe me a new monitor!" :laugh:

Heh... call Grant... I understand that he actually keeps spares now.

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 8

Sorry to be so late in responding. I was on the road last week and not able to chime in.

First, if I offended anyone I apologize. I am a sarcastic twit and I do need to be called on it.

Yes, everyone has a right to their own opinion. There was something said about defending everyone's right to be wrong.

ATBCharles Kincaid

Paul White SSC Guru Points: 150468 More actions · Answer 9

Charles Kincaid (4/20/2009)
First, if I offended anyone I apologize. I am a sarcastic twit and I do need to be called on it. Yes, everyone has a right to their own opinion. There was something said about defending everyone's right to be wrong.

Hey Charles,

Don't worry about it - it was a perfectly valid point to make.

I'm rather a fan of sarcasm.

...or am I ;c)

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Paul White (4/20/2009)
I'm rather a fan of sarcasm.

No way.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150468 More actions · Answer 11

RBarryYoung (4/20/2009)
Paul White (4/20/2009)
I'm rather a fan of sarcasm.
No way.

Monitor sales are up strongly this year, I hear.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Charlie.anna Mr or Mrs. 500 Points: 531 More actions · Answer 12

Great piece of code Jeff but i have tested it with 10000000 records and i am getting Arithmetic overflow error converting data type' :unsure:

I am working a Developer in an E-Commerce project and in one of table we yearly store at least 10000000 records. please check with that number of record.

---------------------------------------------------
The Greatest pleasure in life is doing what people say you can't do! 🙂

MS-ACCESS DBA!! :hehe:

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

Ravi Naik (5/22/2009)
Great piece of code Jeff but i have tested it with 10000000 records and i am getting Arithmetic overflow error converting data type' :unsure:
I am working a Developer in an E-Commerce project and in one of table we yearly store at least 10000000 records. please check with that number of record.

I'm pretty sure that error is probably in the data you're using and not a problem with the code, but I'll check. Thanks for the heads up.

Any chance of you posting the actual error message?

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

Charlie.anna Mr or Mrs. 500 Points: 531 More actions · Answer 14

Hi, Gud morning

i have used the same code for testing with 10 million records, as code itself is inserting records, so i have nothing to do with the data.

the method i am using is "Pre-aggregated" Cross Tab with CTE" and when i am executing this , i am getting the following error msg:

=======================================================

======= "Pre-aggregated" Cross Tab with CTE =====

Msg 8115, Level 16, State 2, Line 5

Arithmetic overflow error converting expression to data type int.

SQL Server Execution Times:

CPU time = 13202 ms, elapsed time = 4838 ms.

========================================================

please note that its working great with less number of records, and i too believe that there is something wrong with data insertion part. I am also trying to find out what is causing error.

Regards,

Ravi

---------------------------------------------------
The Greatest pleasure in life is doing what people say you can't do! 🙂

MS-ACCESS DBA!! :hehe:

Charlie.anna Mr or Mrs. 500 Points: 531 More actions · Answer 15

Jeff,

I was trying to find out the maximum number of records it can work with and its working fine with 6791500 records in my SQL Sever's Server having 8 GB RAM and Quad Core XEON processor.

On strange thing i have noticed about is, i have created physical table and inserted ----

1) 70 million records and tested >> generated error

2) deleted some records>new records number : 6791500 >> generated error

3) I have dropped the table

4) I again inserted 6791500 records >>Working Fine!!! :hehe:

Thank god, i am not a DBA! 😛

waiting for your comment Jeff.

---------------------------------------------------
The Greatest pleasure in life is doing what people say you can't do! 🙂

MS-ACCESS DBA!! :hehe: