Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,481 through 6,495 (of 59,072 total)

Reply To: how to get Pivot result set using 3 tables

p.s. The good part about this is that you reminded me that I needed to update my old Dynamic Cross Tab article. I edited the first post in the discussion...
--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)

September 2, 2020 at 2:50 pm

#3785633
Reply To: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

PLEASE READ ME!
This article is an old one and I've been remiss in not updating it but you need to be aware of something... The creation of the dynamic...
--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)

September 2, 2020 at 2:28 pm

#3785619
Reply To: how to get Pivot result set using 3 tables

Erland Sommarskog wrote:
Jeff Moden wrote:
I generally agree with Erland on this subject and pietLinden is probably correct about SSRS BUT... this is actually fairly simple to pull off in T-SQL.
Yes, for you...
--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)

September 2, 2020 at 2:10 pm

#3785611
Reply To: how to get Pivot result set using 3 tables

Damn... I forgot the GROUP BY. Serves me right for posting so late without any test data to check with. That's no good excuse but those are the reasons.
On the...
--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)

September 2, 2020 at 1:55 pm

#3785599
Reply To: how to get Pivot result set using 3 tables

gaurav wrote:
Please suggest where I need to modify or where I have done wrong. Thank you
gaurav wrote:
No, quantity belongs to tblLocationAssets
Hang on... I'm checking. Heh... this is what I get...
--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)

September 2, 2020 at 1:23 pm

#3785573
Reply To: how to get Pivot result set using 3 tables

Replace the code for @SQL2 and its related comment with this. You might also want to fix the location names in the future... a lot of them have trailing spaces.
--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)

September 2, 2020 at 8:04 am

#3785474
Reply To: how to get Pivot result set using 3 tables

So print out the dynamic SQL and post it. It's likely going to be in line 9 of the Dynamic SQL.

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

September 2, 2020 at 6:18 am

#3785453
Reply To: Using Recommendations for MAXDOP and Max Memory During Installation

p.s. I don't know who left you a rating of "1" but it wasn't me. I generally don't rate articles and I certainly wouldn't rate one so low without an...

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

September 2, 2020 at 6:13 am

#3785448
Reply To: Using Recommendations for MAXDOP and Max Memory During Installation

I'm not sure where the 70-30 recommendation actually came from but I have do disagree with it on large systems. I can't believe it's not a total waste to give...

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

September 2, 2020 at 6:06 am

#3785443
Reply To: Using NOLOCK in UPDATE or DELETE

At this point, it's almost a dead issue but I have to agree with Scott. If you want to say that using WITH (NOLOCK) or setting the transaction isolation level...

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

September 2, 2020 at 5:47 am

#3785440
Reply To: Using NOLOCK in UPDATE or DELETE

pdanes wrote:

Grant Fritchey wrote:

... we get companies that require NOLOCK in every query (and yeah, run across this one many times now).

Seriously? An official, company-wide mandate to DB coders to use 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)

September 2, 2020 at 5:41 am

#3785437
Reply To: Using NOLOCK in UPDATE or DELETE

khushbu wrote:

Any reference link I can refer too and make my point?

Frederico provided one in his post... the very first post after your question.

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

September 2, 2020 at 5:34 am

#3785435
Reply To: how to get Pivot result set using 3 tables

gaurav wrote:

Jeff Moden wrote:

How many rows do you have in the tbl_Projects table?

Sir, there are 51 rows.

Ok... so we definitely need Dynamic SQL, at least to build the query if you...

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

September 2, 2020 at 5:19 am

#3785431
Reply To: Database Modeling - Relationships

I agree. Very nice article and I share Scott's joy in seeing someone make a Bridge/Intersection/Cross Reference/M2M/Cartesian/Relational Multiplication table (heh.. those are just some of the names I've heard for...

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

September 2, 2020 at 4:04 am

#3785424
Reply To: The Coping Thread

Aubrey Love wrote:

Jeff,

I just love that quote. "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

It...

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

September 2, 2020 at 3:38 am

#3785419

Viewing 15 posts - 6,481 through 6,495 (of 59,072 total)