SQL server Multiple rows to one comma separated value

This is the select statement in Sql server which will select the multiple rows of a column into a single column and single row as a comma separated values.

SELECT  ID ,STUFF((SELECT ‘, ‘ + CAST(Name AS VARCHAR(10)) [text()] FROM Customer WHERE GroupId = t.ID FOR XML PATH(”), TYPE).value(‘.’,’NVARCHAR(MAX)’),1,2,’ ‘) List_Output
FROM CustomerGroup t
GROUP BY ID

You can test the functionality here http://sqlfiddle.com/#!6/1cda0e/4

Advertisements

Sql Sever Split String function with special character support

The following the is the SQL server table valued function which will return the result as table. The input to the function is string delimited with the comma (for that matter any character which is is not part of the string it self) and the delimited character. This will also work even if the special characters present in the given input string.

1. Function

Create FUNCTION UdfSplitString
(
   @String       NVARCHAR(MAX),
   @SplitChar  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value(‘(./text())[1]’, ‘nvarchar(4000)’)
      FROM 
      ( 
        SELECT x = CONVERT(XML, ‘<![CDATA[‘ 
          + REPLACE(@String, @SplitChar, ‘]]><![CDATA[‘) 
          + ‘]]>‘).query(‘.’)
      ) AS a CROSS APPLY x.nodes(‘i’) AS y(i)
   );
GO


2. Select Statement

Select * from UdfSplitString(‘1,2,3,*@#$~!%^&*()-+=_{}|’,’,’)

You can also test the functionality from the following link. http://sqlfiddle.com/#!6/e667f/3/0

Reset SQL Server password


To reset the password, we need to set SQL server mode as Single use mode. To do that open a command prompt as an administrator

Type the following commands without the quotes (“ “)

  1. “net stop mssqlserver” and press enter. (mssqlserver is your server instance name)
  2. “net start mssqlserver /m” and press enter
  3. “sqlcmd” and press enter
  4. “CREATE LOGIN tempuser WITH PASSWORD = ‘Password123’ “ and press enter
  5. “GO” and press enter
  6. “sp_addsrvrolemember ‘tempuser’, ‘sysadmin’ “ and press enter
  7. “GO” and press enter
  8. “quit” and press enter
  9. “net stop mssqlserver” and press enter
  10. “net start mssqlserver” and press enter

Now you can login with “tempuser” account with the “Password123” as password and reset sa user account password.