CODEWITHSUNDEEP
sql-server-2008
user580927
user580927

Reputation: 549

How to get unique values from all columns of a table in SQL Server

I have 60 columns and I need to get unique values from all the columns using a SQL query. Is it possible in SQL Server?

Note I need to find distinct values from all the columns

Upvotes: 1

Views: 3109

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

please try this code

declare @Sql_Str varchar(8000)='';
select @Sql_Str=@Sql_Str+' select cast (' +name  +' as varchar(500)) from 
<yourtable> union' from sys.columns where [object_id]=object_id('<yourtable>')
set @Sql_Str=SUBSTRING(@Sql_Str,1,len(@Sql_Str)-6)
exec(@Sql_Str)

Upvotes: 2

pdav221
pdav221

Reputation: 11

  1. Drag the columns heading in the SSMS object explorer to the query window.
  2. Find and replace the ", " with "\n" using Regular Expressions.
  3. Generate a SELECT INTO distinct query for each column using Regular Expressions for each column and assign the output to a temp table built by the Regular Expression.
  4. Make use of the data in your temp tables.

Upvotes: 0

Related Questions