January 5, 2011 at 6:20 pm
Pivot Possibly?
SELECT * FROM #TEMP_Promotions: (Sample Output Below)
PA_EnterpriseIDPP_NamePP_ID
91st Quarter 2010 Remove from PIP Agency Pruning132
93rd Quarter 2009 PIP Agency Pruning112
92nd Quarter 2009 PIP Agency Pruning104
91st Quarter 2009 PIP Agency Pruning 102
93rd Quarter 2008 PIP Agency Pruning98
10Bonus Commission Plan (BCP) 200883
10College World Series Sales Contest 200869
10Bainwest63
10Bonus Commission Plan (BCP) 200761
10NASCAR 200755
10Seminars - 200751
Desired Output:
PA_EnterpriseID, 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th
91st Quarter 2010 Remove from PIP Agency Pruning3rd Quarter 2009 PIP Agency Pruning, ETC
10College 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
SELECTPA_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_EnterpriseID12345
93rd Quarter 2008 PIP Agency Pruning
91st Quarter 2010 Remove from PIP Agency Pruning
92nd Quarter 2009 PIP Agency Pruning
91st Quarter 2009 PIP Agency Pruning
93rd 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_CODECLASSPAYROLLTop 7 Raw
48864940623.001
49014923529.002
49083615000.003
48006221800.004
49403101000.005
4838095000.006
4801774491.007
4906046463.008
4521545491.009
4901533600.0010
4901233600.0010
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