Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Select Command to produce unique value list for all table columns

Posted on 2013-12-11
Medium Priority
471 Views
I need a MS SQL Select Command that will produce a unique set of values from multiple columns.  This SQL Select command needs to read through all 25 columns of data  and provide a list of only the unique values.    For example:

TableA:

Col1        Col2        Col3       Col25
------       --------      --------     -------
Sugar     Honey     Wheat    Milk
Wheat   Sugar       Corn       Salt
Flour     Corn         Honey

Expected Result would be only the unqiue values from all columns:

Result
--------
Sugar
Wheat
Honey
Milk
Salt
Corn
Flour
0
Question by:hojohappy
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 21

Expert Comment

ID: 39713152
You're going to want to unpivot the data then group it. Sorry I don't have time to do that now, but there is the unpivot example which hopefully you can translate into your situation.

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
0

LVL 34

Accepted Solution

Brian Crowe earned 1000 total points
ID: 39713292
SELECT DISTINCT Result
FROM
(
SELECT DISTINCT Col1 AS Result FROM TableA
UNION
SELECT DISTINCT Col2 FROM TableA
UNION
SELECT DISTINCT Col3 FROM TableA
UNION
...
)
0

Assisted Solution

IMSUKH earned 1000 total points
ID: 39713418
SELECT distinct PPID
FROM colmerg
CROSS APPLY (VALUES (col1),
(col2),
(col3),----(col24) ) AS Lines(PPID)
ORDER BY PPID

pls check this
0

LVL 66

Expert Comment

ID: 39714064

What is the logic for the sorting of your expected return set?
0

LVL 70

Expert Comment

ID: 39714620
The CROSS APPLY method shown by IMSUKH should perform the best, by far.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month6 days, 16 hours left to enroll

#### 704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.