Dynamic Numeric List SQL

A few times I've needed to create a numbered list from a SQL script where the underlying table didn't have numeric data types.

Sometimes something like this is from an internal customer, other times it is needed for development purposes. It's a fairly easy script that could be easily implemented so you don't have to make a temp table or variable table just to have a numbered list.

The keyword and syntax is as follows:

ROW_NUMBER() OVER (PARTITION BY <column_name> ORDER BY <column_name>)

That’s it! As a quick example, here it is on the AdventureWorks2012 database. The query:

SELECT p.FirstName, p.LastName, e.JobTitle ,

ROW_NUMBER() OVER (PARTITION BY p.PersonType ORDER BY p.LastName) AS place

FROM person.person p

INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = p.BusinessEntityID

Returns:

Now the PARTITION BY part is kind of like doing a “count()” function when writing an aggregate query in that you are counting the same instances of that column (every person in the above query is an employee and we are numbering each instance of an employee, the numbering process would re-start if we included non-employees).

A fairly simple coding addition that can be included in every query.

Comments

Get Column Names of reports in SQL Server's ReportServer

So here’s the deal. We have a lot of reports at my place of employment. Some reports are exact copies of each other with the exception of a few columns. When users request a new report to be built, we can generally direct them to one of these reports. For our (searching) purposes this can be a bit laborious because we have to open the report to see the SQL so that we can see the columns that are being pulled to see if the columns/data points are what the users are looking for. I figured an easier way of doing this was to search the SQL Server reporting database (from now on referred to as its proper name ReportServer) for column names. I’ll walk through the code here. To start off, I created some variables because I’ll need to do some additional processing in a variable table: DECLARE @t TABLE ( eye_d INT IDENTITY ( 1 , 1 ), ItemID UNIQUEIDENTIFIER , Name VARCHAR ( 500 ), Type INT , typedescription VARCHAR ( 100 ), content IMAG...

Quick Character Script for SQL Server CHAR()

Q uick post - I had to find and replace crazy characters in one of our CHAR, NCHAR , and NVARCHAR data types. Since there are only 256 characters, you'll only get that many results DECLARE @i INT = 0 DECLARE @c TABLE ( char_number INT , char_desc NVARCHAR (1)) WHILE @i < 310 BEGIN INSERT INTO @c(char_number, char_desc) SELECT @i AS eye_d, CHAR (@i) AS char_desc SET @i = ( ISNULL (@i,310) + 1) IF CHAR (@i) IS NULL SET @i = 310 END SELECT * FROM @c

Digital Increment

Search This Blog