January 5, 2011 at 6:20 pm
Pivot Possibly?
SELECT * FROM #TEMP_Promotions: (Sample Output Below)
PA_EnterpriseID PP_Name PP_ID
9 1st Quarter 2010 Remove from PIP Agency Pruning 132
9 3rd Quarter 2009 PIP Agency Pruning 112
9 2nd Quarter 2009 PIP Agency Pruning 104
9 1st Quarter 2009 PIP Agency Pruning 102
9 3rd Quarter 2008 PIP Agency Pruning 98
10 Bonus Commission Plan (BCP) 2008 83
10 College World Series Sales Contest 2008 69
10 Bainwest 63
10 Bonus Commission Plan (BCP) 2007 61
10 NASCAR 2007 55
10 Seminars - 2007 51
Desired Output:
PA_EnterpriseID, 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th
9 1st Quarter 2010 Remove from PIP Agency Pruning 3rd Quarter 2009 PIP Agency Pruning, ETC
10 College World Series Sales Contest 2008, Bainwest, Bonus Commission Plan (BCP) 2007, ETC
I don't know how to even go about accomplishing this, but I want to pull only the most recent 10 PP_Name's based on the PP_ID and return the PP_Name's to Columns 1-10 based on their order by PP_ID.
Any help would be GREATLY Appreciated! Thank you in advance!
January 5, 2011 at 8:30 pm
try this
create table #temp_promp(
PA_EnterpriceID int
, PP_Name varchar(100), PP_ID int
)
insert into #temp_promp
values (9,'1st Quarter 2010 Remove from PIP Agency Pruning',132)
insert into #temp_promp
values (9, '3rd Quarter 2009 PIP Agency Pruning', 112)
insert into #temp_promp
values (9, '2nd Quarter 2009 PIP Agency Pruning', 104)
insert into #temp_promp
values (9, '1st Quarter 2009 PIP Agency Pruning', 102)
insert into #temp_promp
values (9, '3rd Quarter 2008 PIP Agency Pruning', 98)
insert into #temp_promp
values (10, 'Bonus Commission Plan (BCP) 2008', 83)
insert into #temp_promp
values (10, 'College World Series Sales Contest 2008', 69)
insert into #temp_promp
values (10, 'Bainwest', 63)
insert into #temp_promp
values (10, 'Bonus Commission Plan (BCP) 2007', 61)
insert into #temp_promp
values (10, 'NASCAR 2007', 55)
insert into #temp_promp
values (10, 'Seminars - 2007', 51)
select * from
(
select PA_EnterpriceID,PP_Name,ranking
from (
select *, ROW_NUMBER() over (partition by PA_EnterpriceID order by PP_ID desc) as ranking
from #temp_promp) a
where ranking<=10) b
pivot
(
max(PP_Name)
for ranking in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) as P
January 5, 2011 at 8:44 pm
Thanks yewang80 but the values in the column vary, so far I've come up with this, it sucks:
SELECT *, RANK() OVER (PARTITION BY PA_EnterpriseID ORDER BY PP_ID DESC) AS 'Top 10'
INTO #TEMP_Promotions3
FROM #TEMP_Promotions2
SELECT PA_EnterpriseID
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 1),'') AS '1'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 2),'') AS '2'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 3),'') AS '3'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 4),'') AS '4'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 5),'') AS '5'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 6),'') AS '6'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 7),'') AS '7'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 8),'') AS '8'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 9),'') AS '9'
,ISNULL((SELECT PP_NAME WHERE [Top 10] = 10),'') AS '10'
INTO #TEMP_Promotions4
FROM #TEMP_Promotions3
GROUP BY PA_EnterpriseID, PP_NAME, [Top 10]
Now I get this:
PA_EnterpriseID 1 2 3 4 5
9 3rd Quarter 2008 PIP Agency Pruning
9 1st Quarter 2010 Remove from PIP Agency Pruning
9 2nd Quarter 2009 PIP Agency Pruning
9 1st Quarter 2009 PIP Agency Pruning
9 3rd Quarter 2009 PIP Agency Pruning
I dont know how group it....
January 5, 2011 at 8:46 pm
The post doesn't look like I was expecting,
I get PA_EnterpriseID Then 1 - 10 Columns - BUT the PA_EnterpriseID continues to duplicate and I want it to group, I'm getting the data back, only the 10 I want, but I want each PA_EnterpriseID on 1 row
January 5, 2011 at 8:48 pm
See the Attachment
January 6, 2011 at 12:52 am
Please have a look at the CrossTab and DynamicCrossTab articles referenced in my signature.
The CrossTab version for your last query would look like
SELECT PA_EnterpriceID
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '1'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '2'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '3'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '4'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '5'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '6'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '7'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '8'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '9'
,MAX(CASE WHEN [Top 10]=1 THEN PP_NAME ELSE '' END) AS '10'
--INTO #TEMP_Promotions4
FROM #TEMP_Promotions3
GROUP BY PA_EnterpriceID
January 7, 2011 at 4:32 pm
What should I do if it is ranking duplicates the same?
--Apply Raw Rank to Payroll / CC
SELECT *, RANK() OVER (PARTITION BY AGENCY_CODE ORDER BY PAYROLL DESC) AS 'Top 7 Raw'
INTO #TEMP_CLASS3
FROM #TEMP_CLASSIZE
AGENCY_CODE CLASS PAYROLL Top 7 Raw
4 8864 940623.00 1
4 9014 923529.00 2
4 9083 615000.00 3
4 8006 221800.00 4
4 9403 101000.00 5
4 8380 95000.00 6
4 8017 74491.00 7
4 9060 46463.00 8
4 5215 45491.00 9
4 9015 33600.00 10
4 9012 33600.00 10
So since the Payroll is the same for two different class codes it receives the same rank 10... do you know of a way to avoid this?
January 7, 2011 at 4:43 pm
It depends on what you're looking for...
I'd use ROW_NUMBER istead of RANK. This will return values 10 and 11 for 'Top 7 Raw'.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply