I have four different tables in SQL Server for which I'm using left join & inner join to get summarized output on basis of particular column of that table, but I want in output that column should not be repeated. My output is not as expected.
For e.g. A,B,C,D these are the my table where I'm using left join & inner join but I got an output as shown here:
| Acc_no | VPN | VPN_Count |
| ------------ | ------------ | ------------ |
| 12345678 | abc@ybl | 4 |
| 12345678 | bcd@ybl | 4 |
| 12345678 | cde@ybl | 4 |
| 12345678 | def@ybl | 4 |
But I want output to look like this - as one Acc_no
has 4 vpn's that count is 4:
Acc_no | VPN | VPN_Count |
---|---|---|
12345678 | abc@ybl | 4 |
bcd@ybl | ||
cde@ybl | ||
def@ybl |
How to get this output in SQL Server please? Can anyone help me with this?
Thanks in advance
1条答案
按热度按时间whlutmcx1#
As mentioned by siggemannen in their comment one method would be to use
LAG
to check the prior value. To avoid repetition of theOVER
clause I use theWINDOW
clause here, however, if you aren't on 2022+ you'll need to explicitly define theOVER
clause for eachLAG
function.db<<>fiddle