How to retrieve distinct values from multiple columns

Question

Hello Experts,

I have a flat text file data which I import into a SQL Server table.

It creates and table with specified name along with multiple columns as per data file.

Now I need a query which will return the data and its count. e.g.

data file :

BREAD,MILK
BREAD,DIAPER,BEER,EGGS
MILK,DIAPER,BEER,COKE
BREAD,MILK,DIAPER,BEER
BREAD,MILK,DIAPER,COKE 
BREAD,ICE,MANGO
JUICE,BURGER

Result should be

BREAD | 5
MILK  | 4
DIAPER| 4

and so on.

Answer 1

This is a wild guess based on incomplete information:

select Item, count(*)
from Items
group by Item

Answer 2

At a guess at the requirement as would need to see your scheme, but, maybe something like this?

SELECT
    ItemValue,
    COUNT(*)
FROM
(
    SELECT
        Column1 ItemValue
    FROM
        DataTable
    UNION ALL
    SELECT
        Column2 ItemValue
    FROM
        DataTable
    UNION ALL
    SELECT
        Column3 ItemValue
    FROM
        DataTable
    UNION ALL
    SELECT
        Column4 ItemValue
    FROM
        DataTable
) UnionDataTable