Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server 2008+ Get distinct values for each column in a table
--==================================
-- Get a list of distinct values for every column in a table
-- Change the @Table parameter to the one you are interested in.
-- Change the VALUES in @ExcludedColumns if you want to exclude certain columns
--==================================
-- Set the @Table you are interested in
DECLARE @Table Varchar(max) = ''
--Optionally exclude some columns
DECLARE @ExcludedColumns TABLE (ColumnName varchar(128))
INSERT INTO @ExcludedColumns
VALUES
('DWDateCreated')
,('DWDateModified')
-- Don't set these. Just initializing with empty strings to prevent type inference later.
DECLARE @cols varchar(max) = ''
DECLARE @colsValues varchar(max) = ''
DECLARE @sqlselect varchar(max) = ''
DECLARE @sqlunpivot varchar(max) = ''
SELECT
@cols += ',
' + QUOTENAME(name),
@colsValues += ',
CAST(' + QUOTENAME(name) + ' As varchar(100)) as ' + QUOTENAME(name)
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@Table)
AND name NOT IN (Select ColumnName from @ExcludedColumns)
SET @sqlselect = 'SELECT DISTINCT
''' + @Table + ''' as SourceTable,
ColumnName,
Value
FROM
(
SELECT ' + STUFF(@colsValues,1,1, '') + '
FROM ' + @Table + '
) as Source
'
SET @sqlunpivot =
'UNPIVOT
(
value for ColumnName in (' + STUFF(@cols,1,1, '') + ')
) as up'
+ ' Order by ColumnName, value '
exec (@sqlselect + @sqlunpivot)
@davoscollective

This comment has been minimized.

Copy link
Owner Author

commented Nov 18, 2015

Result set will be two columns, first column will hold the column names from the original table, second column will be the distinct values in that column.

A business analyst asked for this information and this seemed to be a quick way to get the data out, I'm sure there are other applications.

@luchero21

This comment has been minimized.

Copy link

commented Sep 23, 2019

you can help me this outcome insert in table with cursor for multiple tables, thank you

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.