Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Roll up of a column in dynamic query Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 3:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:56 AM
Points: 12, Visits: 26
HI
This is data in the table

ClientId Ad TagId Name Total
1 D1 47 American 1000
1 D2 47 American 500
1 D3 47 American 300
1 D4 48 Hispanic 1000
1 D5 48 Hispanic 200

This is the result when I pivot the table

ClientId TagId Name D1 D2 D3 D4 D5
1 47 American 1000 500 300 0 0
1 48 Hispanic 0 0 0 1000 200


you can copy the following code

create table #TestTable
(
ClientId int
,Ad varchar(10)
,TagID int
,[Name] varchar(10)
,Total int

)
Insert #TestTable values (1 , 'D1', 47, 'American', 1000)
Insert #TestTable values (1 , 'D2', 47, 'American', 500)
Insert #TestTable values (1 , 'D3', 47, 'American', 300)
Insert #TestTable values (1 , 'D4', 48, 'Hispanic', 1000)
Insert #TestTable values (1 , 'D5', 48, 'Hispanic', 200)


DECLARE @columns VARCHAR(8000)
DECLARE @columns2 VARCHAR(8000)
DECLARE @sql NVARCHAR(MAX)

SET @Columns = SUBSTRING((SELECT DISTINCT ',['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)

SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+Ad+'],0) AS ['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)

SET @SQL = 'SELECT Clientid, TagId, [Name],' + @Columns2 + ' FROM
(Select Clientid, TagId, [Name], ad, Total from #TestTable ) SourceData
PIVOT
(min(Total) for ad in ('+@Columns+')) pivottable
Order by ClientId, TagId '
EXEC(@sql)

But I want the the roll up of the colums.. The desired output would be like this.
Cid TagId Name D1 D2 D3 D4 D5
1 47 American 1000 500 300 0 0
1 47 American 1000 500 300 0 0
1 47 ALL 2000 1000 600 0 0
1 48 Hispanic 0 0 0 1000 200
1 48 Hispanic 0 0 0 1000 200
1 48 ALL 0 0 0 2000 400

please help me
Post #1388502
Posted Wednesday, November 28, 2012 8:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 69, Visits: 363
google for Cube and Rollup in sql, might be that's your answer
Post #1389831
Posted Wednesday, November 28, 2012 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:56 AM
Points: 12, Visits: 26
Yeah Roll up works here. I have already tried.. but it is not working in inner query. So I have taken another outer select query and got the desired result.. thank u
Post #1389843
Posted Wednesday, November 28, 2012 8:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:56 AM
Points: 12, Visits: 26
Now I have to get the row sum of each column from D1 to D4.

Example: the desired result set would be like the below

Cid TagId Name D1 D2 D3 D4 D5 tot(D1andD2) tot(D3andD4)
1 47 American 1000 500 300 0 0 1500 300
1 47 American 1000 0 300 0 500 1000 800

Any idea?
Post #1389854
Posted Wednesday, November 28, 2012 9:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 69, Visits: 363
create table #TestTable
(
ClientId int
,Ad varchar(10)
,TagID int
,[Name] varchar(10)
,Total int

)
Insert #TestTable values (1 , 'D1', 47, 'American', 1000)
Insert #TestTable values (1 , 'D2', 47, 'American', 500)
Insert #TestTable values (1 , 'D3', 47, 'American', 300)
Insert #TestTable values (1 , 'D4', 48, 'Hispanic', 1000)
Insert #TestTable values (1 , 'D5', 48, 'Hispanic', 200)


DECLARE @columns VARCHAR(8000)
DECLARE @columns2 VARCHAR(8000)
declare @TotalX int
DECLARE @sql NVARCHAR(MAX)

SET @Columns = SUBSTRING((SELECT DISTINCT ',['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)

SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+Ad+'],0) AS ['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)

set @TotalX = 3

SET @SQL = 'SELECT Clientid, TagId, [Name],' + @Columns2 + '
FROM
(Select Clientid, TagId, [Name], ad, Total from #TestTable ) SourceData
PIVOT
(min(Total) for ad in ('+@Columns+')) pivottable
Order by ClientId, TagId '

--EXEC(@sql)
--drop table #TestTable


CREATE TABLE #Temp1
(
clientid int,
tagid int,
tname varchar(max),
d1 int,
d2 int,
d3 int,
d4 int,
d5 int

)

insert into #temp1
EXEC(@sql)
declare @counter varchar(25)

SET @Counter = 1

DECLARE @ColumnCreator NVARCHAR(MAX)

WHILE @Counter <= @TotalX
BEGIN
SET @ColumnCreator = 'ALTER TABLE #Temp1 ADD DTotal'+ @Counter + ' DECIMAL(18,2)'
EXEC (@ColumnCreator)
SET @Counter = @Counter + 1
END



DECLARE @TotalCreator NVARCHAR(MAX)
declare @i varchar(5)
set @i = 1
declare @i1 varchar(5)
set @i1 = 2

SET @TotalCreator ='update #temp1 set dtotal'+@i+' = d'+ @i + '+ d'+ @i1

exec(@TotalCreator)
set @i = @i+1
set @i1 = @i1+1

SET @TotalCreator ='update #temp1 set dtotal'+@i+' = d'+ @i + '+ d'+ @i1

exec(@TotalCreator)
set @i = @i+1
set @i1 = @i1+1

SET @TotalCreator ='update #temp1 set dtotal'+@i+' = d'+ @i + '+ d'+ @i1

exec(@TotalCreator)
set @i = @i+1
set @i1 = @i1+1


select * from #temp1


drop table #TestTable
drop table #Temp1
Post #1389916
Posted Thursday, November 29, 2012 2:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:56 AM
Points: 12, Visits: 26
Thank you very much for your help... but I cant create a table with the columns D1, D2 etc like..
CREATE TABLE #Temp1
(
clientid int,
tagid int,
tname varchar(max),
d1 int,
d2 int,
d3 int,
d4 int,
d5 int

)


since the number of those columns will change. every month the additional column will add to the table like D6, D7 etc.
Post #1390354
Posted Thursday, November 29, 2012 2:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 69, Visits: 363
you can make dynamic.
Post #1390359
Posted Thursday, November 29, 2012 3:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:56 AM
Points: 12, Visits: 26
have got the desired result.. Thank you very much
Post #1390370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse