T-SQL count number of different values across multiple columns-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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