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