August 25, 2011 at 2:30 pm
Have dimension meta data exported out of oracle hyperion financial management system. The data pertains to Account dimensions and their associated custom fields. 1 account can have different combinations of custom fields associated with it. For my purposes, i need select 1 account dimension and custom for every different account in the data set. i have provided data below to show what i need.
Account ICP Custom1 Custom2
---------------- ------------- ------------ --------------
AC_12345 ICP2 Cust1gftr Cust2abcd
AC_12345 ICP3 Custppoe Cust2efgh
AC_12345 ICP1 Cust1uytr Cust2abcd
AC_54321 ICP3 Cust1cvbc Cust2asas
AC_54321 ICP5 Cust1fghr Cust2qwas
AC_54321 ICP7 Cust1popo Cust2ioty
AC_54321 ICP4 Cust1kiuo Cust2mnjk
AC_34253 ICP8 Cust1pxza Cust2sfer
AC_34253 ICP9 Cust1pipp Cust2llly
I would like to extract 1 row for account AC_12345, AC_34253 and AC_54321
An example output would be:
Account ICP Custom1 Custom2
---------------- ------------- ------------ --------------
AC_12345 ICP2 Cust1gftr Cust2abcd
AC_54321 ICP5 Cust1fghr Cust2qwas
AC_34253 ICP9 Cust1pipp Cust2llly
It doesn't matter which row is picked as long there is 1 row for each individual account.
August 25, 2011 at 2:43 pm
One method is to use Row_number to get that
;with cte ( Account ,ICP ,Custom1,Custom2) AS
(
SELECT 'AC_12345','ICP2','Cust1gftr','Cust2abcd'
UNION ALL SELECT 'AC_12345','ICP3','Custppoe','Cust2efgh'
UNION ALL SELECT 'AC_12345','ICP1','Cust1uytr','Cust2abcd'
UNION ALL SELECT 'AC_54321','ICP3','Cust1cvbc','Cust2asas'
UNION ALL SELECT 'AC_54321','ICP5','Cust1fghr','Cust2qwas'
UNION ALL SELECT 'AC_54321','ICP7','Cust1popo','Cust2ioty'
UNION ALL SELECT 'AC_54321','ICP4','Cust1kiuo','Cust2mnjk'
UNION ALL SELECT 'AC_34253','ICP8','Cust1pxza','Cust2sfer'
UNION ALL SELECT 'AC_34253','ICP9','Cust1pipp','Cust2llly'
),
numbered as
(
select *, RN = ROW_NUMBER() OVER( PARTITION BY Account order by (select null) )
from cte
)
select *
from numbered
where rn = 1
If the row that is to be picked matters, then handle it via ORDER BY clause int he ROW_NUMBER clause.
August 25, 2011 at 2:51 pm
thanks a lot. that worked perfectly.
August 25, 2011 at 3:04 pm
You're welcome.
This is method 2, using Cross Apply and Distinct 🙂
;with cte ( Account ,ICP ,Custom1,Custom2) AS
(
SELECT 'AC_12345','ICP2','Cust1gftr','Cust2abcd'
UNION ALL SELECT 'AC_12345','ICP3','Custppoe','Cust2efgh'
UNION ALL SELECT 'AC_12345','ICP1','Cust1uytr','Cust2abcd'
UNION ALL SELECT 'AC_54321','ICP3','Cust1cvbc','Cust2asas'
UNION ALL SELECT 'AC_54321','ICP5','Cust1fghr','Cust2qwas'
UNION ALL SELECT 'AC_54321','ICP7','Cust1popo','Cust2ioty'
UNION ALL SELECT 'AC_54321','ICP4','Cust1kiuo','Cust2mnjk'
UNION ALL SELECT 'AC_34253','ICP8','Cust1pxza','Cust2sfer'
UNION ALL SELECT 'AC_34253','ICP9','Cust1pipp','Cust2llly'
),
distinctAccts as
(
select distinct Account
from cte
)
select CrsApp.Account ,CrsApp.ICP ,CrsApp.Custom1,CrsApp.Custom2
from distinctAccts outertable
Cross Apply
( select Top 1 Account ,ICP ,Custom1,Custom2
from cte innertable
where innertable.Account = outertable.Account
) CrsApp
Happy learning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply