help with data manipulation

• AppSup_dba

  SSCrazy

  Points: 2215

  More actions

  June 5, 2010 at 7:48 am

  #233450

  Hi Experts,

  Please help!!

  Issue:

  We have a complex stored procedure which returns data in format.

  partnercodepartnernameskunogs1no

  1707 aditya 2303100

  1707 aditya 2303200

  1707 aditya 2303300

  1707 aditya 2303400

  1800 mirza 2304500

  2398 charlie 2305700

  1710 tango 23061100

  1710 tango 23062200

  1710 tango 23063300

  I need this data to be in format:

  partnercodepartnernameskunogs1no

  1707 aditya 2303100

  - - -200

  - - -300

  - - -400

  1800 mirza 2304500

  2398 charlie 2305700

  1710 tango 23061100

  - - -2200

  - - -3300

  Rule:

  All duplicate rows (without considering the gs1no) should be shown as ‘null’, except the first row.

  My Approach:

  I am transferring the rows from SP to #table named ‘#gs1’ and then I am trying to manipulate it.

  Meta Data:

  ------- table definition-----

  create table #gs1

  ( partnercode int,

  partnername varchar(20),

  skuno varchar (20),

  gs1no varchar (20)

  )

  ---------sample data to simulate the issue-----------

  insert into #gs1

  select 1707,'aditya','2303','100'

  union all

  select 1707,'aditya','2303','200'

  union all

  select 1707,'aditya','2303','300'

  union all

  select 1707,'aditya','2303','400'

  union all

  select 1800,'mirza','2304','500'

  union all

  select 2398,'charlie' ,'2305','700'

  union all

  select 1710,'tango', '2306','1100'

  union all

  select 1710,'tango','2306','2200'

  union all

  select 1710,'tango','2306','3300'

  Addition info:

  1)One Skuno can have multiple gs1no but not vice versa.

  2)We are displaying the data fetched from the SP into a report, now this report can be seen by many vendors simultaneously.

  3)The number of rows returned by the SP is approximately 65k, so performance is also a concern.

  I will be happy to provide any more information if required.

  Any more efficient approach to resolve the issue can be also be also be adopted.

  Cheers

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  June 6, 2010 at 11:22 pm

  #1176936

  Hi there,

  I'm just trying to help.. Maybe, you can try this out:

  ;WITH mycte AS (

  SELECT partnercode,partnername,skuno,gs1no,ROW_NUMBER() OVER (PARTITION BY partnercode ORDER BY LTRIM(RTRIM(gs1no))) row_num

  FROM #gs1 gs1

  )

  SELECT CASE WHEN row_num=1 THEN partnercode ELSE NULL END

  , CASE WHEN row_num=1 THEN partnername ELSE NULL END

  , CASE WHEN row_num=1 THEN skuno ELSE NULL END

  , gs1no

  FROM mycte

  thanks!

• AppSup_dba

  SSCrazy

  Points: 2215

  More actions

  June 7, 2010 at 3:20 am

  #1176973

  Hi Shield,

  Your logic was excellent... and will help me achieve what i wanted.

  I learnt something new today 🙂

  One more query :

  Can we use CTE with SP, as in

  ;WITH cte

  AS

  (exec sp_abc)

  ?

  Cheers

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  June 9, 2010 at 8:09 pm

  #1178389

  Hi ankur,

  My apologies for my late reply.. I was not able to follow this thread..

  It's a pleasure to help.. Regarding your question, I think it would not be possible to execute stored procedure in CTE.. Other alternative though is to convert your stored procedure into inline table-valued function..

  Thanks!