March 14, 2011 at 11:11 am
Just for further references, sql 7, 2000 forum is here...
March 14, 2011 at 11:14 am
LutzM (3/14/2011)
jason-772533 (3/14/2011)
I checked the 'Forums' list before I posted and couldn't see a 2K forum. I've just checked again and there isn't one listed. There is an 'Older versions' forum but this doesn't stipulate 2KI don't think it's required to repost the question over there... Maybe Steve can move the thread?
Regarding the issue you're struggling with: I would look into the quirky update method.
An example can be found in Jeffs article[/url]. I don't think there's an easier way to simulate ROW_NUMBER() in SS2K.
I'd also like to know if the table structure can be modfied to hold an additional column with the row number we need to create. Otherwise we have to use a temp table. In that case, the nmber of rows in your original table would be a helpful information, too.
March 14, 2011 at 11:24 am
jason-772533 (3/14/2011)
Thanks Jeff . . . it was puzzling me a bit, but makes sense now - the technique isn't using MAX in its classic sense.However, I still don't think this technique fits with my scenario, because although the maximum number of 'category' values per 'description' is 10, across the full dataset there may be over a hundred distinct 'category' values.
Can you think of any technique that can accommodate this, so that even if two 'descriptions' have two different 'categories' they are still returned in columns 2 and 3, i.e.
A Apple Aardvark
B Banana Baboon
Any help greatly appreciated.
Can I just take this opportunity to thank you personally Jeff for all your articles on SSC. They have become an invaluable reference source for me as I have taught myself SQL over the past 18 months, and have pride of place as .doc files in my 'SQL Learning' folder.
Regards . . . Jason
Hey Jason, I see what you're trying to do here, and pivoting isn't pretty in 2k... not that Pivoting it in 2k5+ is better, but at least it's easier to see what's going on.
The notes are in the code and it uses a serial update (Quirky Update to most folks, I just don't always follow all the rules, so I don't use that name). Let me know what questions you have. Please note, this isn't pretty to the optimizer. It will chug like mad against a real table of significant size.
if object_id('tempdb..#tmp') is not null
drop table #tmp
if object_id('tempdb..#AddRowNumbering') is not null
drop table #AddRowNumbering
create table #tmp (category varchar(2) not null,
DataEntry varchar(30) not null)
insert into #tmp VALUES ( 'A', 'apple')
insert into #tmp VALUES ( 'A', 'aardvark')
insert into #tmp VALUES ( 'A', 'angora')
insert into #tmp VALUES ( 'A', 'applesauce')
insert into #tmp VALUES ( 'A', 'archives')
insert into #tmp VALUES ( 'A', 'amplomb')
insert into #tmp VALUES ( 'A', 'argument')
insert into #tmp VALUES ( 'A', 'aggressive')
insert into #tmp VALUES ( 'A', 'AT-AT')
insert into #tmp VALUES ( 'A', 'amorphous')
insert into #tmp VALUES ( 'A', 'ablative')
insert into #tmp VALUES ( 'A', 'asbestos')
-- 12 entries, we want ten. Now, for a shorter cat list
insert into #tmp VALUES ( 'B', 'basketball')
insert into #tmp VALUES ( 'B', 'baboon')
insert into #tmp VALUES ( 'B', 'barber')
insert into #tmp VALUES ( 'B', 'bacon') -- cause everything's better with bacon...
insert into #tmp VALUES ( 'B', 'babble')
insert into #tmp VALUES ( 'B', 'bouncing buttered baby boy')
-- 6 items in the category.
-- Now, we're going to assume that this data is our SELECT list, so we'll build from here.
CREATE TABLE #AddRowNumbering
(RowNum INT IDENTITY( 1,1) NOT NULL,
ModifiedrowNum INT NULL, -- Note the NULL here, it's important
category Varchar(2) NOT NULL,
DataEntry Varchar(30) NOT NULL)
CREATE CLUSTERED INDEX idx_AddRowNumbering on #AddRowNumbering (RowNum)
INSERT INTO #AddRowNumbering (category, DataEntry)
SELECT DISTINCT
category, dataentry
from
#tmp
-- The following structure imitates a ROWNUMBER() OVER (PARTITION BY Category ORDER BY RowNum)
DECLARE @CategoryCounter INT,
@Category VARCHAR(2)
SET @CategoryCounter = 0
SET @Category = ''
UPDATE#AddRowNumbering
SET@CategoryCounter = CASE WHEN @Category = Category
THEN @CategoryCounter
ELSE RowNum
END,
ModifiedRowNum = RowNum - @CategoryCounter,
@Category = Category
FROM
#AddRowNumbering
OPTION (MAXDOP 1)
-- Show the results actually occuring as expected, don't use this in the final product.
SELECT * FROM #AddRowNumbering
-- Now, we manually create our pivot.
SELECT DISTINCT
Category,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 0 AND arn2.Category = arn.category) AS Cat1,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 1 AND arn2.Category = arn.category) AS Cat2,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 2 AND arn2.Category = arn.category) AS Cat3,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 3 AND arn2.Category = arn.category) AS Cat4,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 4 AND arn2.Category = arn.category) AS Cat5,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 5 AND arn2.Category = arn.category) AS Cat6,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 6 AND arn2.Category = arn.category) AS Cat7,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 7 AND arn2.Category = arn.category) AS Cat8,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 8 AND arn2.Category = arn.category) AS Cat9,
(SELECT DataEntry FROM #AddRowNumbering AS arn2 WHERE ModifiedRowNum = 9 AND arn2.Category = arn.category) AS Cat10
from
#AddRowNumbering AS arn
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 14, 2011 at 12:12 pm
As was brought to my attention (thanks WayneS!) I've neglected to include an @Anchor column in the above script, which helps protect you from wonky index usage. I personally have never needed it but I always work off #Tmps with a single clustered index for running totals as a safety net. This, however, may not be your case.
So, modify the update code as follows as a self defense mechanism, note the change for @Anchor. The TABLOCKX is just there to speed up locking mechanics, as the table itself is dedicated to this process.
-- The following structure imitates a ROWNUMBER() OVER (PARTITION BY Category ORDER BY RowNum)
DECLARE @CategoryCounter INT,
@Category VARCHAR(2),
@Anchor INT
SET @CategoryCounter = 0
SET @Category = ''
UPDATE#AddRowNumbering
SET@CategoryCounter = CASE WHEN @Category = Category
THEN @CategoryCounter
ELSE RowNum
END,
ModifiedRowNum = RowNum - @CategoryCounter,
@Category = Category,
@Anchor = RowNum
FROM
#AddRowNumbering WITH ( TABLOCKX)
OPTION (MAXDOP 1)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 15, 2011 at 2:18 am
Thanks everyone for the replies.
Lutz . . No, the table structures can't be modified. I'm a BI analyst, so only have read access to the tables, so have to use temp tables, although I do have a BI database to which I have read/write access that I use for my tally tables etc. Regarding the base table row counts, my 'description' table has approx 60,000 rows, and my category table has approx 100 rows, and I have to join the two tables via three linking tables.
Craig - that works a treat. It'll probably take me a little time to figure out exactly how it does it, but I'm sure I'll get there. As you say - it's not exactly straightforward in S2K! And thanks for the category choices in your demo code - they definitely raised a smile on a dreary Tuesday a.m!
Rgds . . . Jason
March 15, 2011 at 6:12 am
jason-772533 (3/15/2011)
It'll probably take me a little time to figure out exactly how it does it, but I'm sure I'll get there.
Hi Jason,
Lutz provided a link to an article which explains it. It all works very much like doing such a thing in C# or whatever.
A word of caution... the method can go haywire if you don't follow the rules for its use. The rules and how it works are in an article (currently being revamped) that Lutz provided the link for. Here it is again.
http://qa.sqlservercentral.com/Forums/Topic1077293-338-1.aspx#bm1078177
--Jeff Moden
March 15, 2011 at 7:21 am
Wow . . . 22 pages. That'll keep me off the streets tonight.
Thanks again Jeff. I'm pretty certain this is a technique I need to understand - especially considering I have some users who what reports output in columns rather than rows ! (this job would be great if it wasn't for the end users 🙂
March 15, 2011 at 7:25 am
Hmm. It's the end users that keep you in a job... 😀
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply