How to retrieve distinct values from multiple columns

Question

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.

That's the problem with not normalized databases, everything gets more difficult. — Otávio Décio
– Otávio Décio, Commented Apr 21, 2010 at 14:44
@Otavio: we don't know if the database is denormalized or not. The data could be transformed in any number of ways during import. — D'Arcy Rittich
– D'Arcy Rittich, Commented Apr 21, 2010 at 14:45

Robin Day · Accepted Answer · 2010-04-21 14:49:59Z

1

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

answered Apr 21, 2010 at 14:49

Robin Day

103k23 gold badges122 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Claudia Over a year ago

UnionDataTable is an Alias for the previous SELECT.

D'Arcy Rittich · Accepted Answer · 2010-04-21 14:43:51Z

0

This is a wild guess based on incomplete information:

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

answered Apr 21, 2010 at 14:43

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

1 Comment

ANIL MANE Over a year ago

Do you think I can achieve this using cursor or similar, because I don't know how much columns would be for different data files?

Collectives™ on Stack Overflow

How to retrieve distinct values from multiple columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related