code grouping sets

declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=5
set @numrows3=2
set @numrows4=3


create table #c1(Id int,Name varchar(300),Value int)
create table #cc1(Id int,Name varchar(300),Value int)
INSERT INTO #c1
Select 1,'testc1',9
union
Select 1,'testc1',8.5

declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from #c1 Order by NEWID()'
insert into #cc1
Execute (@vSQL)
update #cc1 set Name=Name+'T11'




create table #c2(Id int,Name varchar(300),Value int)
create table #cc2(Id int,Name varchar(300),Value int)
declare @vSQL2 varchar(1000)
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' * from #c2 Order by NEWID()'
INSERT INTO #c2
Select 2,'1testc2',8
union
Select 2,'2testc2',8
union
Select 2,'3testc2',7.5
union
Select 2,'4testc2',8.5
union
Select 2,'5testc2',8.5
union
Select 2,'6testc2',7
union
Select 2,'7testc2',7.5
insert into #cc2
Execute (@vSQL2)
update #cc2 set Name=Name+'T12'

create table #c3(Id int,Name varchar(300),Value int)
create table #cc3(Id int,Name varchar(300),Value int)
declare @vSQL3 varchar(1000)
INSERT INTO #c3
Select 3,'1testc3',8
union
Select 3,'2testc3',8.5
union
Select 3,'3testc3',8.5

select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from #c3 Order by NEWID() '

INSERT INTO #cc3
Execute (@vSQL3)
update #cc3 set Name=Name+'T13'


create table #c4(Id int,Name varchar(300),Value int)
create table #cc4(Id int,Name varchar(300),Value int)
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from #c4 Order by NEWID()'
insert into #c4
Select 4,'1testc4',8
union
Select 4,'2testc4',8
union
Select 4,'3testc4',8
union
Select 4,'4testc4',8.5
union
Select 4,'5testc4',8.5
union
Select 4,'6testc4',7
union
Select 4,'7testc4',8.5
union
Select 4,'8testc4',8.5
union
Select 4,'9testc4',7
union
Select 4,'10testc4',8

insert into #cc4
Execute (@vSQL4)
update #cc4 set Name=Name+'T14'

Declare @tot int

Select @tot=sum([Sumvalue]) from (
select  sum(Value)[Sumvalue] from #cc1
union
select sum(Value)[Sumvalue] from #cc2
union
select sum(Value)[Sumvalue] from #cc3
 union
select sum(Value)[Sumvalue] from #cc4
)as B



select A.Id,A.Name,sum(distinct A.Sumvalue) [Sum],@tot Total
from
(
select  Id,Name,sum(Value)[Sumvalue] from #cc1
group by Id,Name
union
select Id,Name,sum(Value)[Sumvalue] from #cc2
group by Id,Name
union
select Id,Name,sum(Value)[Sumvalue] from #cc3
group by Id,Name
union
select Id,Name,sum(Value)[Sumvalue] from #cc4
group by Id,Name
)as A
group by A.Id,A.Name


--compute sum(A.Sumvalue)

--drop table #c1
--drop table #c2
--drop table #c3
--drop table #c4
--drop table #cc1
--drop table #cc2
--drop table #cc3
--drop table #cc4



Declare @t table(Sno int identity(1,1),Name nvarchar(200),Score int,servers varchar(100))

insert into @t
Select 'master',15,'TS3'
union
Select 'master',85,'TS4'
union
Select 'master',0,'TPCDB'
union
Select 'master',31,'SHELL1P1'
union
Select 'tempdb',10,'TS3'
union
Select 'tempdb',0,'TS4'
union
Select 'tempdb',3,'TPCDB1'
union
Select 'tempdb',4,'SHELL1P1'

select * from @t

SELECT Name,
stuff(
(
    SELECT ','+ CAST(Score AS varchar) + '('+ISNULL(servers,'DNP')+')'
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'')  resultswithservers
Into #comma
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name



SELECT Name,
stuff(
(
    SELECT ','+ CAST(Score AS varchar)
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'')  results
into #ct
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name

;WITH ValList AS(
        SELECT  Name,
                CAST(LEFT(results,PATINDEX('%,%', results) - 1) AS INT) Val,
                RIGHT(results,LEN(results) - PATINDEX('%,%', results)) Remainder,results
        FROM    #ct
        UNION ALL
        SELECT  Name,
                CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS INT) Val,
                RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder,results
        FROM    ValList
        WHERE   LEN(Remainder) > 0

)
SELECT  V.Name,results,resultswithservers,
        SUM(Val)[Sumofcomma],AVG(Val) [AVG]
FROM    ValList V
join #comma C On V.Name=C.Name
GROUP BY V.Name,results,resultswithservers


drop table #ct
drop table #comma

How to change SQL Server Collation change and Database Collation for Default and Named Instance in SQL Server

To rebuild system databases for an instance of SQL Server for Server Collation change

Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

To see logs of summary after execute command below location

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Default instance

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domainname\Administrator /SAPWD= Pas$w0rd /SQLCOLLATION=SQL_Latin1_General_CP850_BIN


Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domainname\Administrator  /SAPWD= user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS


Named Instance


Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS=Domain_name\Administrator  /SAPWD=user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

some time we will get error The Windows account admin-pc\administrator does not exist and cannot be provisioned as a SQL Server system administrator we need to use "" on account as below

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="Domain-name\Administrator " /SAPWD=user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS




Pre Implementation Steps


Step 1:

Take the Backup of system and user databases and logins using sp_helprevlogin script with privilges and linked servers

Implementation Steps:

Step 1:

Log in to the domainname/administrator account to the machine/RDP which needs to change server collation

Step 2: verify the instance running default (MSSQLServer) or Named instance from SQL Configuration manager

Step 3:

Open explorer and navigate to the Setup.exe of SQL Server from following location

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release (or)

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2 (or)

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012


Step 4:

Open command prompt from Run command from start menu


Type cd with path like below and press enter

C:\Users\Documents>CD C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2


Step 5: Once got into the above location from command prompt

type below command press enter

Default instance

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domainname\Administrator /SAPWD= Pas$w0rd /SQLCOLLATION=SQL_Latin1_General_CP850_BIN

Named Instance

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS=domain_name\administrator /SAPWD=user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

some time we will get error The Windows account admin-pc\administrator does not exist and cannot be provisioned as a SQL Server system administrator we need to use "" on account as below

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="domain-name\administrator" /SAPWD= user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

ex:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="domain-name\administrator" /SAPWD= user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Step 6:

Now once finished above command command prompt will come to another line with blank


Step 7:

We can check the logs from below location from summary.txt file


C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt

To see error log below location dont confuse

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log


To change collation of Database

ALTER DATABASE dbname
COLLATE CollationName;

Ex

USE master;
GO
ALTER DATABASE dbname
COLLATE French_CI_AS ;
GO

Note;
if you change the database collation you need to check whether column of table collation also changed or not. If not you need to change one by one use below script

USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

before change collation of column you need to verify following

You cannot change the collation of a column that is currently referenced by any one of the following:

A computed column

An index

Distribution statistics, either generated automatically or by the CREATE STATISTICS statement

A CHECK constraint

A FOREIGN KEY constraint

When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.

see for more

https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017


---------------------Verify the collation setting for database
SELECT name, collation_name
FROM sys.databases
WHERE name = N'dbname';


---------------------Alter the collation setting for database before verify existing --------------------

Declare @TableName Nvarchar(Max)
Declare @CollationName Nvarchar(Max)
Declare @ColumnName Nvarchar(Max)
Declare @SQLText Nvarchar(Max)
Declare @DataType Nvarchar(Max)
Declare @CharacterMaxLen Nvarchar(Max)
Declare @IsNullable Nvarchar(Max)


set @CollationName='SQL_Latin1_General_CP850_BIN'

DECLARE  MyTableCursor CURSOR FOR

SELECT TABLE_NAME
             from INFORMATION_SCHEMA.TABLES


   --Verify whether given collation existing on column or not
SELECT Distinct TABLE_NAME,COLLATION_NAME from INFORMATION_SCHEMA.Columns where COLLATION_NAME='SQL_Latin1_General_CP850_BIN'

OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS
            WHERE table_name = @TableName AND  Data_Type LIKE '%char%'
            OR Data_Type LIKE '%text%' AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN

            SET @SQLText = 'ALTERTABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END +
              ') COLLATE ' + @CollationName + ' ' +
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END

           

            PRINT @SQLText

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor

PRINT @SQLText

FETCH NEXT FROM MyTableCursor INTO @TableName

END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Ref;
https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2014

https://www.sanssql.com/2012/10/how-to-change-server-collation.html

https://www.sanssql.com/2013/10/change-sql-server-collation-without.html

Named instance error

https://dba.stackexchange.com/questions/143246/error-when-trying-to-install-sql-server-2012-ent-in-to-a-server-core-2008-vm