SQL Server How to get summarize output basis on particular column without repetition of that column?

vsikbqxv  于 2023-04-28  发布在  其他
关注(0)|答案(1)|浏览(90)

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_noVPNVPN_Count
12345678abc@ybl4
bcd@ybl
cde@ybl
def@ybl

How to get this output in SQL Server please? Can anyone help me with this?

Thanks in advance

whlutmcx

whlutmcx1#

As mentioned by siggemannen in their comment one method would be to use LAG to check the prior value. To avoid repetition of the OVER clause I use the WINDOW clause here, however, if you aren't on 2022+ you'll need to explicitly define the OVER clause for each LAG function.

SELECT CASE V.Acc_no WHEN LAG(V.Acc_no) OVER Prev THEN NULL ELSE V.Acc_no END AS Acc_no,
       CASE V.VPN WHEN LAG(V.VPN) OVER Prev THEN NULL ELSE V.VPN END AS VPN,
       CASE V.VPN_Count WHEN LAG(V.VPN_Count) OVER Prev THEN NULL ELSE V.VPN_Count END AS VPN_Count
FROM (VALUES(12345678,'abc@ybl',4),
            (12345678,'bcd@ybl',4),
            (12345678,'cde@ybl',4),
            (12345678,'def@ybl',4))V(Acc_no,VPN,VPN_Count)
WINDOW Prev AS (PARTITION BY Acc_no ORDER BY VPN, VPN_Count);

db<<>fiddle

相关问题