CODEWITHSUNDEEP
sqlsql-server-2005
ANIL MANE
ANIL MANE

Reputation: 1405

How to retrieve distinct values from multiple columns

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.

Upvotes: 0

Views: 338

Answers (2)

Robin Day
Robin Day

Reputation: 102458

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

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

This is a wild guess based on incomplete information:

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

Upvotes: 0

Related Questions