CrossTab..
-
Gurus,
I've SQL Server 2000 Table like this
Cl: Business: Source: Analyst: Times:
GE -- Analytics -- Peer -- Chayan -- Once
AA -- Collections -- Third -- Remya -- Twice
I want Output like this
Cl: Business: Source: Chayan: Remya:
GE -- Analytics -- Peer -- Once -- Nil
AA -- Collections -- Third -- Nil -- Twice
I seek your precious help
Rajesh N.
-
If you want to work smart check out RAC @
If you want to be smart in your work check out:
http://www.beyondsql.blogspot.com
-
SQL 2000:
select ci, business, source, (case analyst when 'Chayan' then times else 'Nil' end) as Chyan,
(case analyst when 'Remya' then times else 'Nil' end) as Remya
from your table
--James
-
What? No shameless product spam, James?
--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: I checked out his login and he seems to only be able to promote one single product. It doesn't matter what the problem/question is the solution is always his product.
Rog: There is not much that is more annoying then needing help with something and being told the solution is to BUY another product. That really is not much help. I'd be more impressed if you offered a REAL solution, then pointed to a product that makes the solution easier to implement. The other REAL problem with using a third party product to do your thinking for you, is that if you ever change employers you are then stuck begging the new employer to buy an additional product, without which your productivity may be in the toilet (along with your career).
JM2C,
James.
-
Perfect... Couldn't have said it better... and have been through this with these folks more than once. Almost nothing worse than folks that spam forums.
--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) -
JLKs method works if you have a known set of data that you want to crosstab. If it will vary this procedure works pretty well although it uses a cursor. I'm still waiting for someone smarter than me (Jeff??) to write it without the cursor.
ALTER PROCEDURE [createpivot]
@dbname varchar(8000), --database table is in
@pivotrows varchar(8000), -- comma delimted rows to group by
@pivottable varchar(8000),-- table you want to crosstab
@pivotdata varchar(8000), -- field to be summed
@pivotcols varchar(8000), -- column to be pivoted
@pivotfunc varchar(8000) -- function to be performed
as declare
@sqltxt varchar(8000),
@sqlstmt varchar(8000),
@pivotcolumn varchar(8000)
/*do not edit below this line*/
exec('use ' + @dbname)
set @sqltxt = 'select '+@pivotrows+','
set @sqlstmt = 'declare pivot_cursor cursor for select distinct('+@pivotcols+') from '+@pivottable
exec(@sqlstmt)
open pivot_cursor
fetch next from pivot_cursor
into @pivotcolumn
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
set @sqltxt = @sqltxt + ' ' + @pivotfunc + '(case '+ @pivotcols +' when '''+ @pivotcolumn + ''' then '+ @pivotdata +' else 0 end) as ['+ @pivotcolumn +'],'
FETCH NEXT FROM pivot_cursor
into @pivotcolumn
END
CLOSE pivot_cursor
DEALLOCATE pivot_cursor
set @sqltxt = left(@sqltxt,len(@sqltxt)-1)
set @sqltxt = @sqltxt + 'from ' + @pivottable + ' group by ' + @pivotrows + ' order by ' + @pivotrows
/*print @sqltxt*/
exec(@sqltxt)
-
Well Rac covers a lot of different problems.
Do you make the same argument against using Red-Gate software? I doubt it. Then why the double standard? All you have to do is look at Rac Help to see how every possible dynamic crosstab is easily solved. Or perhaps you don't want anyone offering relief from PMS (pivot madness syndrome).
-
Jeff is a smart guy, and he helped me out in this post.
It may not be applicable to the original post, (but it could be
), but it's still a quality piece of SQL.Dave J
-
Thank you Gurus (Genius).
-
You are correct.... I don't make the same argument against Redgate because they ligitimately advertise instead of resorting to spamming a forum. Your product is well founded... your advertising method sucks.
--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) -
Thank you for the high favor Mr. Polecat.
David is correct, though... take a peek at the link he provided for the "unstable" version
David, thank you for the high praise, my friend.
--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) -
I don't have any problems with tools, but what I don't need when I have a question is someone telling me to buy a tool. As I said before, help me solve the problem with the tools I have and THEN feel free to suggest a tool that might make my life easier. </rant>
James.
-
I agree with Jeff. Forum posters benefit the forum by providing help and are benefitted by the forum by recieving help. Advertisers benefit the forum by paying for the upkeep of the forum and are benefitted by forum users buying their product. Win-win for all involved.
Those who post their advertising without paying or providing help attempt to benefit from the forum without returning anything to the community. Similar to a leech or a tapeworm.
-
I looked at the procedure when the thread first posted. I even tried to combine mine and yours together because I need something more dynamic. I'm still working on taking this
--===== Build the select LIST (do not try to reformat or you'll mess it up!)
SELECT @MySQL2 = ISNULL(@MySQL2 + ',','') + '
MIN(CASE WHEN AttributeName = ''' + AttributeName + '''
THEN AttributeValue ELSE NULL END) AS ' + '[' + AttributeName + ']'
FROM (SELECT DISTINCT TOP 100 PERCENT AttributeName AS AttributeName
FROM #Results
Order by AttributeName) d
and getting it to work with supplied variables like mine does.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply