1

I have a table like below

enter image description here

The main idea is to get the amount of each channel for each orderID.

If the channel is repeating for Id, it should take the amount only once and rest would be null.

The result should look like below

enter image description here

I want to do the same logic for country and source as well. If I do the pivot it should only give unique amount value for each ID on Channel, Source and Country basis and repeated value should be NULL.I have a very big data set and tough to do it in Excel

How can I do this in SQL Server? Is there any window function? Can anyone please help me?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user123
  • 31
  • 1
  • 7
  • 2
    How is your data sorted? Try `case when row_number() over (partition by orderID order by whatever) = 1 then Amount end` – dnoeth May 29 '18 at 20:58
  • it is not working, it ignore other value for same id. – user123 May 29 '18 at 21:15
  • Ups, of course, you must add the channel to the partitioning: `case when row_number() over (partition by orderID, Channel order by Channel) = 1 then Amount end` – dnoeth May 29 '18 at 21:27

0 Answers0