Jeff Moden

Forum Replies Created

Viewing 15 posts - 49,186 through 49,200 (of 59,099 total)

RE: How to Pivot a table

tony.sawyer (7/17/2008)
I find Jeffs answer one that works for this example - but I'm wondering how to do something a bit more...
The solution provided used a case statement where 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)

July 17, 2008 at 12:08 pm

#844576
RE: How to Pivot a table

I'm in the process of writing an article about Crosstabs and Pivots... hope to finish it soon but one of the things I'm finding out in testing is that, except...
--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)

July 17, 2008 at 6:04 am

#844308
RE: Need to remove duplicate records and get distinct values

Jeff Moden (7/16/2008)
karthikeyan (7/16/2008)
Jeff,
how about my code ?
What size column do you thing NAME = '' will make? Also, look at the update... which NAME shall it use to...
--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)

July 17, 2008 at 5:54 am

#844296
RE: How to Pivot a table

The best thing for this is a "Crosstab Report"... and it's easier than the PIVOT operator...
--===== Create and populate a test table
-- THIS IS NOT...
--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)

July 16, 2008 at 9:17 pm

#844124
RE: can i avoid cursor?

This is right on the border line of me thinking this is some type of homework... please post a decent try!
I will give you a hint, though... lookup GROUP BY.
--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)

July 16, 2008 at 5:35 pm

#844102
RE: Need to remove duplicate records and get distinct values

karthikeyan (7/16/2008)
which one is good when we think performance ?
Try it... let us know...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has...
--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)

July 16, 2008 at 8:45 am

#843828
RE: Need to remove duplicate records and get distinct values

karthikeyan (7/16/2008)
Jeff,
how about my code ?
What size column do you thing NAME = '' will make? Also, look at the update... which NAME shall it use to do 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)

July 16, 2008 at 8:40 am

#843823
RE: remove spaces more than double

RyanRandall (7/14/2008)
Another technique which can be useful in some circumstances...
update #TEST set Descr = replace(replace(replace(Descr, ' ', '¬ '), ' ¬', ''), '¬', '') where Descr like '% %'
(Where...
--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)

July 16, 2008 at 8:07 am

#843790
RE: Need to remove duplicate records and get distinct values

Ok... now that I've had a bit of coffee... 😛
In the original problem posted, San didn't care about which name was returned so just use the MIN or Max name...
--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)

July 16, 2008 at 7:37 am

#843755
RE: Need to remove duplicate records and get distinct values

Dugi (7/16/2008)
Hi Jeff!
Your code here just selecting and ordering asc the data inside the table it doesn't do any elimination of duplicate records!
Can you explain little bit this!
Dugi
I believe that...
--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)

July 16, 2008 at 7:17 am

#843745
RE: Need to remove duplicate records and get distinct values

Dugi (7/16/2008)
Hi Jeff!
Your code here just selecting and ordering asc the data inside the table it doesn't do any elimination of duplicate records!
Can you explain little bit this!
Dugi
Heh... Sure! ...
--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)

July 16, 2008 at 7:15 am

#843741
RE: Split data

I wouldn't recommend that way... it has a While loop in it and a couple of other performance related problems. Try the following, please... it also explains how 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)

July 15, 2008 at 11:45 pm

#843521
RE: Need to remove duplicate records and get distinct values

There are a couple of ways... I believe the fastest for large data sets in SQL Server 2000 would be in the following...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--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)

July 15, 2008 at 11:39 pm

#843519
RE: CPU 100% Lots of Loks. tempdb.dbo.#TMP_CLIENTS_PROCESSING

riga1966 (7/15/2008)
Hi Jeff,
I think you are right.
I replaced UPDATE code and ran it.
But it now gets stuck inside a loop.
I run this SP a a script. I put lots of...
--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)

July 15, 2008 at 1:45 pm

#843382
RE: Need to remove duplicate records and get distinct values

Heh... you guys are missing the real problem! 😉 The real problem is why does the OP NOT care about which data is returned? If you don't care,...
--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)

July 15, 2008 at 12:12 pm

#843315

Viewing 15 posts - 49,186 through 49,200 (of 59,099 total)