Tags » SQL

A nifty little tool to find all the distinct values in all columns

Jorick van der Hoeven - 31/01/2017 - Comments off

A few months ago I became quite frustrated because I had a huge backlog of work to do performing the Quality Control of the analyses of my team members. A large portion of performing this type of Quality Control was getting a sense of the data ingested by the analyses and determining if there were any data-specific elements of the code which were not appropriately commented or if there were strange data points in the output data. To do this I needed to quickly get a sense for the possible values in an unfamiliar table, doing the same thing over and over again gets tedious, so I wrote this little script to allow me to quickly get all the distinct values in a column for every column in a table. I’ve provided the code to do this below with explanatory comments.

-----------------------------------------
-- DISTINCT COLUMN QUERY
-----------------------------------------
DECLARE --Variable to hold our table specification
        @tblSpec  NVARCHAR(MAX), 
        --Variable listing all unwanted columns in the table
        @unwantedColumns NVARCHAR(MAX) 
                  

SET @tblSpec = ''

--##List any columns you don't want the distinct values from here
SET @unwantedColumns = '' 

--Check if the temp table exists already, if so, boot it
IF OBJECT_ID('tempdb..#unwantedcols') IS NOT NULL 
  DROP TABLE #unwantedcols

--Create a table with all the unwanted columns to ignore
CREATE TABLE #unwantedCols
(colname nvarchar(max))

--This is a table valued function which splits a string, 
--there are plenty of examples online for how to write one of these
INSERT INTO #unwantedCols(colname)
SELECT splitdata from fnSplitString(@unwantedColumns,'.') 

--Now is where the magic starts, we start to build our query. 
--What we are building here are a series of temporary tables 
--which split out each of the columns and counts the number 
--distinct elements in that column using the row_number() 
--function
SELECT @tblSpec = @tblSpec+' '+ 
'SELECT [' + COLUMN_NAME +']
      , [ID] = ROW_NUMBER() OVER ( ORDER BY ['+ COLUMN_NAME+'])
INTO [#'+COLUMN_NAME+']
FROM [' +TABLE_NAME+']
GROUP BY ['+COLUMN_NAME+']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tablename
and COLUMN_NAME not in (SELECT colname from #unwantedCols)

--Next we create a backbone for the distinct column table 
--which is a sequential list of numbers as long as the 
--number of rows in the table.
SET @tblSpec += '
SELECT
id = ROW_NUMBER() OVER (order by newID())
INTO #seed
from ['+ @tablename+'] '

--We then build a massive select statement which lists 
--all of the columns and left joins each of the 
--temporary tables created above.

--To start we build the first part of the select with 
--our seed backbone column the list of numbers as 
--long as the number of rows in the table we are analysing.
SELECT top 1
@tblSpec = @tblSpec+' '+
'SELECT #seed.id '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tablename
and COLUMN_NAME not in (SELECT colname from #unwantedCols)
order by ORDINAL_POSITION asc

--We then add each of the column names we want to 
--analyse to our select statement
SELECT
@tblSpec = @tblSpec+' '+
',[#' +COLUMN_NAME+'].['+COLUMN_NAME+'] '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tablename
and COLUMN_NAME not in (SELECT colname from #unwantedCols)
order by ORDINAL_POSITION asc

--We start on the FROM section of the select statement 
--with our seed/backbone temp table
SET @tblSpec += ' FROM #seed '

--We then left join all of our distinct column tables 
--to the seed/backbone 
SELECT
@tblSpec = @tblSpec+' '+
' LEFT JOIN [#' +COLUMN_NAME+'] ON [#'+COLUMN_NAME+'].id=#seed.id '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tablename
and COLUMN_NAME not in (SELECT colname from #unwantedCols)
order by ORDINAL_POSITION asc

--Finally we make our massive select statement ordered 
--by the seed/backbone to make sure that all of our 
--distinct values are near the top
SET @tblSpec += ' ORDER by 1'

-- And finally, we execute the entire creation, 
--temp tables, seed table, and massive select statement.
EXEC (@tblSpec)

-----------------------------------------

-- DISTINCT COLUMN QUERY

-----------------------------------------

DECLARE --Variable to hold our table specification

@tblSpec NVARCHAR(MAX),

--Variable listing all unwanted columns in the table

@unwantedColumns NVARCHAR(MAX)

SET @tblSpec = ''

--##List any columns you don't want the distinct values from here

SET @unwantedColumns = ''

--Check if the temp table exists already, if so, boot it

IF OBJECT_ID('tempdb..#unwantedcols') IS NOT NULL

DROP TABLE #unwantedcols

--Create a table with all the unwanted columns to ignore

CREATE TABLE #unwantedCols

(colname nvarchar(max))

--This is a table valued function which splits a string,

--there are plenty of examples online for how to write one of these

INSERT INTO #unwantedCols(colname)

SELECT splitdata from fnSplitString(@unwantedColumns,'.')

--Now is where the magic starts, we start to build our query.

--What we are building here are a series of temporary tables

--which split out each of the columns and counts the number

--distinct elements in that column using the row_number()

--function

SELECT @tblSpec = @tblSpec+' '+

'SELECT [' + COLUMN_NAME +']

, [ID] = ROW_NUMBER() OVER ( ORDER BY ['+ COLUMN_NAME+'])

INTO [#'+COLUMN_NAME+']

FROM [' +TABLE_NAME+']

GROUP BY ['+COLUMN_NAME+']'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@tablename

and COLUMN_NAME not in (SELECT colname from #unwantedCols)

--Next we create a backbone for the distinct column table

--which is a sequential list of numbers as long as the

--number of rows in the table.

SET @tblSpec += '

SELECT

id = ROW_NUMBER() OVER (order by newID())

INTO #seed

from ['+ @tablename+'] '

--We then build a massive select statement which lists

--all of the columns and left joins each of the

--temporary tables created above.

--To start we build the first part of the select with

--our seed backbone column the list of numbers as

--long as the number of rows in the table we are analysing.

SELECT top 1

@tblSpec = @tblSpec+' '+

'SELECT #seed.id '

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@tablename

and COLUMN_NAME not in (SELECT colname from #unwantedCols)

order by ORDINAL_POSITION asc

--We then add each of the column names we want to

--analyse to our select statement

SELECT

@tblSpec = @tblSpec+' '+

',[#' +COLUMN_NAME+'].['+COLUMN_NAME+'] '

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@tablename

and COLUMN_NAME not in (SELECT colname from #unwantedCols)

order by ORDINAL_POSITION asc

--We start on the FROM section of the select statement

--with our seed/backbone temp table

SET @tblSpec += ' FROM #seed '

--We then left join all of our distinct column tables

--to the seed/backbone

SELECT

@tblSpec = @tblSpec+' '+

' LEFT JOIN [#' +COLUMN_NAME+'] ON [#'+COLUMN_NAME+'].id=#seed.id '

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@tablename

and COLUMN_NAME not in (SELECT colname from #unwantedCols)

order by ORDINAL_POSITION asc

--Finally we make our massive select statement ordered

--by the seed/backbone to make sure that all of our

--distinct values are near the top

SET @tblSpec += ' ORDER by 1'

-- And finally, we execute the entire creation,

--temp tables, seed table, and massive select statement.

EXEC (@tblSpec)

Categories : Snippets, SQL
Tags : SQL, Tips

Creating anonymized pseudo-random IDs

Jorick van der Hoeven - 31/01/2017 - Comments off

I sometimes need to convert a series of unique sensitive IDs into a list of unique IDs which I can use to share with people who aren’t allowed to know the IDs in the first list. The very easiest was to do that in SQL is to either use the HASHBYTES function in T-SQL and provide it with the SHA512 hashing algorithm or to use the t-SQL newID function to create new GUIDs. The difficulty with this is that it creates a very difficult to read ID so if I started with relatively simple to remember 5 character IDs I end up with a super user unfriendly 40 character hexadecimal number.

So how could one scramble a friendly 6 character ID into an anonymous friendly 7 character ID? The easiest way to do this is to use some basic number theory and combine that with SQL’s NEWID() function.

We start off by getting a unique list of all the ID numbers we want to anonymize and scramble the order by using NEWID()

Transact-SQL

Select ID, RandomRow = ROW_NUMBER() OVER (ORDER BY NEWID()) INTO RandOrder FROM ListOfIDs

1
2
3
4

Select ID,
RandomRow = ROW_NUMBER() OVER (ORDER BY NEWID())
INTO RandOrder
FROM ListOfIDs

We then Take that randomly ordered list and perform some math on the order using two carefully selected primes, one is a small prime the number of digits that we want the result to be in (A), and the other is a very large prime (B). I’ve chosen only a middlingly large prime here to make reading easier.
A = 7957597
B = 362736035870515331128527330659
We multiply these in the following way to create a unique list of numbers. NB, prime A must be larger than the total number of IDs you want to create or else this doesn’t work.

DECLARE @A INT = 7957597
DECLARE @B INT = 362736035870515331128527330659 

Select ID,
       Number = (RandomRow * @B) % @A
INTO RandomNums
FROM RandOrder

DECLARE @A INT = 7957597

DECLARE @B INT = 362736035870515331128527330659

Select ID,

Number = (RandomRow * @B) % @A

INTO RandomNums

FROM RandOrder

If we want to be fancy we can convert the integer into hex and add a prefix to denote that this is an anonymous ID. NB the size of your VARBINARY depends on the size you want your strings to be.

Select ID,
       AnonymousID = 'RANDID' + 
          RIGHT(
           master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(8), Number))
          ,7)
FROM RandomNums

Select ID,

AnonymousID = 'RANDID' +

RIGHT(

master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(8), Number))

,7)

FROM RandomNums

Categories : Snippets, SQL
Tags : SQL, Tips

Pivoting a table when you don’t know how many columns you need with dynamic SQL

Jorick van der Hoeven - 11/06/2014 - Comments off

Here’s the situation, you’ve got a column in a table which you want to pivot on but you don’t have any control over the distinct number of values that can be in that column. It could be 3 or it could be 20. However, you still need to be able to pivot the whole table to display the information to the user. How do you do this?

The answer here lies in the way you can use string variables as code in dynamic SQL, and there are two parts of the pivot code which have become variables in the situation outlined above:

SELECT
    UnPivotedColumn1,
    --Variable Block #1
    PivotVal1,
    ...
    PivotValX
    --End: VB #1
FROM (
    SELECT UnPivotedColumn1
                ,PivotSource --Contains values PivotVal1 - X
                ,ColToAggregate
    FROM TableYouWantToPivot
) tbl
PIVOT
(
SUM(ColToAggregate)

FOR PivotSource 
IN ( 
   --Variable Block #2
   [PivotVal1], ... , [PivotValX]
   --End: VB #2 
   )

) pvt

SELECT

UnPivotedColumn1,

--Variable Block #1

PivotVal1,

...

PivotValX

--End: VB #1

FROM (

SELECT UnPivotedColumn1

,PivotSource --Contains values PivotVal1 - X

,ColToAggregate

FROM TableYouWantToPivot

) tbl

PIVOT

(

SUM(ColToAggregate)

FOR PivotSource

IN (

--Variable Block #2

[PivotVal1], ... , [PivotValX]

--End: VB #2

)

) pvt

Therefore, to create this pivot when we have a variable number of values in our PivotSource column we need to populate two strings with the distinct values of the PivotSource Column — Variable Block #1 and Variable Block #2.

To do this we will use the method I explained in an earlier post. For Variable Block #1 the code looks like:

DECLARE @VB1 VARCHAR(MAX);

SET @VB1 = STUFF(
          SELECT ', ' + PivotSource
          FROM TableYouWantToPivot
          FOR XML PATH(''),
          1,2,'')

DECLARE @VB1 VARCHAR(MAX);

SET @VB1 = STUFF(

SELECT ', ' + PivotSource

FROM TableYouWantToPivot

FOR XML PATH(''),

1,2,'')

The stuff command here removes the comma at the beginning so that we have a comma-separated list of values and no trailing comma.

The code for Variable block #2 is similar but formatted slightly differently.

DECLARE @VB1 VARCHAR(MAX);

SET @VB1 = STUFF(
          SELECT ', [' + PivotSource + ']'
          FROM TableYouWantToPivot
          FOR XML PATH(''),
          1,2,'')

DECLARE @VB1 VARCHAR(MAX);

SET @VB1 = STUFF(

SELECT ', [' + PivotSource + ']'

FROM TableYouWantToPivot

FOR XML PATH(''),

1,2,'')

Finally we need to construct our Dynamic SQL statement that we need to execute:

DECLARE @SQLPivot VARCHAR(MAX)

SET @SQLPivot = '
  SELECT
      UnPivotedColumn1,
      ' + @VB1 + ' 
  FROM (
      SELECT UnPivotedColumn1
                  ,PivotSource --Contains values PivotVal1 - X
                  ,ColToAggregate
      FROM TableYouWantToPivot
  ) tbl
  PIVOT 
  (
  SUM(ColToAggregate)

  FOR PivotSource 
  IN ( 
     ' + @VB2 + '
     )

) pvt '

EXEC @SQLPivot

DECLARE @SQLPivot VARCHAR(MAX)

SET @SQLPivot = '

SELECT

UnPivotedColumn1,

' + @VB1 + '

FROM (

SELECT UnPivotedColumn1

,PivotSource --Contains values PivotVal1 - X

,ColToAggregate

FROM TableYouWantToPivot

) tbl

PIVOT

(

SUM(ColToAggregate)

FOR PivotSource

IN (

' + @VB2 + '

)

) pvt '

EXEC @SQLPivot

And there you have it, a Pivot statement that can pivot a table if you don’t know how many variables will be in the column you want to pivot on.

-J

Categories : Snippets, SQL
Tags : SQL, Tips

How to include several values in one field in SQL

Jorick van der Hoeven - 22/10/2012 - Comments off

I found out the other day how to solve a problem that had been causing me issues when presenting data. I often got requests to resent data in such a way that we display information from a table in a grouped form while still allowing someone to consult all of the values relating to this grouping.

The solution isn’t very machine friendly and is end-user oriented. The code can be seen below:

SELECT 
    ta.a1,
    ta.a2,
    ta.a3,
    ( SELECT 
          tta.a4,
      FROM TABLE_A tta
      WHERE 
          ta.a1=tta.a1 
          and ta.a2=tta.a2
          and ta.a3=tta.a3
      ORDER BY tta.a4
      FOR XML PATH('')
    ) AS a4plus
FROM TABLE_A ta

SELECT

ta.a1,

ta.a2,

ta.a3,

( SELECT

tta.a4,

FROM TABLE_A tta

WHERE

ta.a1=tta.a1

and ta.a2=tta.a2

and ta.a3=tta.a3

ORDER BY tta.a4

FOR XML PATH('')

) AS a4plus

FROM TABLE_A ta

Categories : Snippets, SQL
Tags : SQL, Tips

A function similar to first or last from access in MS SQL

Jorick van der Hoeven - 12/10/2012 - Comments off

I’ve been looking for a while now to find a way to return just one row when the fields I am using split up my table return several rows per unique field combination. I found this little gem today:

 ]Select 
   t1,
   t2,
   t3
From
(
   Select 
      t1,
      t2,
      t3,
      row_number() over (Partition by t1 order by t1) rowrank
   from tTable
)
Where rowrank &lt;=1

]Select

t1,

t2,

From

(

Select

t1,

t2,

t3,

row_number() over (Partition by t1 order by t1) rowrank

from tTable

)

Where rowrank <=1

This of course does not emulate first and last exactly as it doesn’t really allow you to influence the order of the returned fields in the partitioning. It is however very useful when you just want to return one value.

Categories : Snippets, SQL
Tags : SQL, Tips