Over Partition

Question

Hi,

Using the following logic,

select name,ref,a.date,  
	case when lap = '1' and syst = '1' then sys_boolean else 0 end as isnew,  
	case when lap = '2' and syst = '1' then sys_Text else '' end as category  
from [cf].[dbo].[cust] as a  
left join [cf].[dbo].[emp] as b on a.id = b.id  
GO  

my table output shows as follows:

However, I need the output to show on 1 row as the above is duplicating records:

Any idea how to do this, please?

Answer 1

Hi,

(1) Next time when you use code then please use the button for code so the text will be formatted well. I edited your message and did it for you this time :-)

(2) In order to text a solution we need a table and data. Please provide queries to CREATE the table and INSERT some sample data. Next you need to present the expected result set according to the sample data, so we will be able to discuss it without the need to read minds or play "the guessing game"

(3) In the meantime, In general, you can wrap your code inside a CTE and aggregate the data in the external query

Read more here about using CTE.

Finally, there is something not clear in your request. It is simple to aggregate the values by the columns name,ref,a.date and use SUM function to get the aggregated value for the final isnew column, but not cl,ear to me how you chose the value of the category if you have for example two or more different categories

Please explain the logic to get the final result and don't forget to provide the missing information as mentioned above

Answer 2

 select name,ref,a.date,
     Max(case when lap = '1' and syst = '1' then sys_boolean else 0 end) as isnew,
      Max(case when lap = '2' and syst = '1' then sys_Text else '' end) as category
 from [cf].[dbo].[cust] as a
 left join [cf].[dbo].[emp] as b on a.id = b.id
 group by name,ref,a.date

Answer 3

Hi，@Bone_12

Welcome to Microsoft T-SQL Q&A Forum!

First, when completing this code , you need to explain the data in sys_Text . Your code direction will explain that there are only two kinds of data in this field: sys and spaces .
If this is the case , you can try to run the following code.

;with cte as  
(  
select name,ref,a.date,  
     max(case when lap = '1' and syst = '1' then sys_boolean else 0 end) as isnew,  
     case when (lap = '2' and syst = '1') then sys_Text else '' end as category  
 from [cf].[dbo].[cust] as a  
 left join [cf].[dbo].[emp] as b on a.id = b.id  
 group by name,max(case when lap = '1' and syst = '1' then sys_boolean else 0 end),a.date,category  
 )  
 select  name,ref,a.date,isnew, distinct(Replace(category,' ','sys')) from cte  

Just a reminder: if this doesn't do what you want, please attach your DDL statement so we can test it.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.