SQL Script to Create Roles,Schemas, Add users and Associate Schema to Role

/* SQL Script to Create Roles, Schemas and users to the same */

--Creation of Database Role and Adding User's begin

USE [YOURDATABASENAME]

GO

CREATE ROLE [YOURROLENAME]

GO

      --Adding Users Begin

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME1'

      GO

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME2'

      GO

      --Adding Users End

      --Similarly Multiple users can be added using EXEC sp_addrolemember

--Creation of Database Role and Adding User's End

--Creation of Schema Starts

USE [YOURDATABASENAME]

GO

CREATE SCHEMA [YOURSCHEMANAME] AUTHORIZATION [YOURSCHEMANAME]

GO

--Creation of Schema End

--Association of Created Schema to Role Starts

USE [YOURDATABASENAME]

GO

ALTER AUTHORIZATION ON SCHEMA::[YOURSCHEMANAME] TO [YOURROLENAME]

GO

--Association of Created Schema to Role End

Grouping data from multiple rows into single row

Often we come across situation where we need to group data from multiple rows into single row. E.g.: An Employee works for multiple projects, so in this case if columns are EMPLOYEE_NAME, PROJECT_DURATION, PROJECTS_WORKED and think that employee works for 10 projects. Then you would have 10 rows containing EMPLOYEE_NAME as same and then 10 different projects. If you now want to have a single row with Employee name and then 10 subsequent name delimited in single column, unfortunately we don’t have any function like GROUP_CONCAT as in MySQL for SQL server. But not to worry this can be achieved by using XML PATH. Below is the example .

Insert into EMPLOYEE_PROJECT(EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME)

SELECT EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME=

REPLACE(

REPLACE(

(SELECT REPLACE(PROJECT_NAME, ' ', '')

AS [data()] FROM EMPLOYEE_PROJECT_DETAILS

WHERE EMPLOYEE_NAME=a.EMPLOYEE_NAME

FOR XML PATH('')),' ',' ; '),'',' ')

FROM EMPLOYEE_PROJECT_DETAILS a WHERE EMPLOYEE_NAME IS NOT NULL GROUP BY EMPLOYEE_NAME,PROJECT_DURATION

Small query but very useful.

Happy Coding

Technorati Tags: Group,Grouping,XML PATH