Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Roll up of a column in dynamic query


Roll up of a column in dynamic query

Author
Message
SSSsil174
SSSsil174
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 40
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
Conficker
Conficker
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 386
google for Cube and Rollup in sql, might be that's your answerw00t
SSSsil174
SSSsil174
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 40
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
SSSsil174
SSSsil174
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 40
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?
Conficker
Conficker
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 386
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
SSSsil174
SSSsil174
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 40
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.
Conficker
Conficker
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 386
you can make dynamic.
SSSsil174
SSSsil174
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 40
have got the desired result.. Thank you very much
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search