T-SQL: Concatenate Multiple Rows of a Column into One Cell

Simple: You have a column and you want to take all rows as one cell. So that you can do searching, ordering, whatever you like.

Solution: Declare a variable, in this case VARCHAR or NVARCHAR, then SELECT the variable by equaling [itself] plus [column name] plus [your seperation char].

Here’s the code snippet:

DECLARE @concatenated NVARCHAR(1000)
SET @concatenated = ''
SELECT @concatenated = @concatenated + [NAME_COLUMN] + ',' FROM [USERS_TABLE] WHERE [ID_COLUMN] < 100
SELECT @concatenated
Post a comment

T-SQL: How to Order by Surname in Full Name Column?

Sometimes you need to do ordering by in a full name column. If you want to order by first name, that’s cool, but when you need to order by last name, you need to find the surname for each field.

Let’s take a look at this data:

[Full Name]
—————————-
Albert Einstein
Leonardo da Vinci
Nicolas Tesla

 
These values are ordered by first name so there’s no problem with that. But if we wanted to order by last name(which is not a particular column in this table), we had to find a way like splitting these fields or something else.

Anyway, there was a good line of code in StackOverflow (see question&answer here) written by user Recep.

SELECT [Full Name], REVERSE(SUBSTRING(REVERSE([Full Name]), 0, CHARINDEX(' ', REVERSE([Full Name])))) AS SurnameAfterReversing
FROM GENIUS_TABLE
ORDER BY REVERSE(SUBSTRING(REVERSE([Full Name]), 0, CHARINDEX(' ', REVERSE([Full Name]))))
--We need to select our ORDER BY expression as a new column otherwise it will warn that it can't order by a column which is not exist in query result.

It reverses the whole name here, then gets text until the first space (‘ ‘) character, then reverses the new text back. That way, we actually get the last name so you can do anything you want with this column.

And here is the data as a result of this query:

[Full Name] SurnameAfterReversing
—————————- —————————-
Albert Einstein Einstein
Nicolas Tesla Tesla
Leonardo da Vinci Vinci
1 Comment

Generate Random Number and String in MS SQL Server

Sometimes you might wanna generate random number or string in MS SQL. For example, I do it for temporary user passwords.

Here’s how you generate a random number. X is the max value, the code is generating between 0 and (X-1).

SELECT CONVERT(INT, X*RAND())

Be aware that max. generated number is X-1 because it’s generating X numbers starting from 0(zero).

If you generate 5 numbers, your range will be: {0, 1, 2, 3, 4}.

 

Another code is generating random string(VARCHAR). Here it is:

SELECT SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 9)

Attention to 0, 9. It’s substring range, here I’m generating a string value whose length is 10 characters.

1 Comment

How to Find Installed Microsoft SQL Server 2008 Product Serial Key

Recently I had to find my original product key for MS SQL Management Studio 2008 in order to make a new installation, but I lost the key information I stored somewhere. So I tried to get the key from already installed product. First, I found the query for getting the key for Studio 2005, then following comments were including the key for 2008, but in 164-byte binary value… After more Googling, I found this great post at Branch Bits and it was sharing a program that converts this binary value to product key! Anyway so I used it and installed successfully.
Here’s the query that gets the serial number in binary format:

use master
GO
exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup','DigitalProductID'
GO

And this is the source link.

And here is the link that you can get the project and use it for converting serial number:
Branch Bits – Finding SQL Server 2008 product key from an installed instance

P.S. For those who needs to find key of 2005 instance may use the free software named ProduKey. It works well for me.
Product CD Key Viewer
For further info, please refer to this question:
Question: SQL Server 2005 cd Key

6 Comments