T-SQL count number of different values across multiple columns

user_4222108 0
Viewed 50

I have an SQL database which contains a table with four different currency columns, I need to determine how many different currencies each record has across these four columns, for example..

Record ID Curr1 Curr2 Curr3 Curr4 CurrencyCount
1 GBP USD GBP GBP 2
2 GBP EUR GBP USD 3
3 GBP GBP GBP GBP 1
4 GBP GBP GBP EUR 2

How can I determine the "CurrencyCount" / count of currencies against each record? I can't think how to approach this

Thank you for any help

tsql sql
3 Answers

You can use a Table Value Constructor referencing columns from the outer row and then COUNT (DISTINCT - no need for any expanding out and collapsing with GROUP BY

SELECT *,
       CurrrencyCount = (SELECT COUNT (DISTINCT Currency) FROM   (VALUES (CURR1), (CURR2), (CURR3), (CURR4)) AS x (Currency))
FROM   YourTable
user_732260

This would do the work. But not sure how will be the performance if there is a large amount of data.

WITH CTE as (
SELECT '1' as RecID,'GBP' as CURR1,'USD' as CURR2,'GBP' as CURR3, 'GBP' as CURR4
UNION ALL
SELECT '2'  ,'GBP'  ,'EUR'  ,'GBP','USD'
UNION ALL
SELECT '3'  ,'GBP'  ,'GBP'  ,'GBP','GBP'
UNION ALL
SELECT '4'  ,'GBP'  ,'GBP'  ,'GBP','EUR'
)

SELECT 
RecID
,CURR1,CURR2,CURR3,CURR4
,COUNT(DISTINCT Currency) as CurrrencyCount
FROM CTE
CROSS APPLY (VALUES (CURR1), (CURR2), (CURR3), (CURR4)) as x (Currency)
GROUP BY RecID,CURR1,CURR2,CURR3,CURR4
user_174455110

Only run this its also work

SELECT 
RecordID
,Curr1,Curr2,Curr3,Curr4
,COUNT(DISTINCT Currency) as Currency
FROM  [TABLE_NAME]
CROSS APPLY (VALUES (Curr1), (Curr2), (Curr3),(Curr4)) as x (Currency)
GROUP BY RecordID,Curr1,Curr2,Curr3,Curr4
user_168841300
Related