November 26, 2012 at 3:48 am
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
November 28, 2012 at 8:25 am
google for Cube and Rollup in sql, might be that's your answer:w00t:
November 28, 2012 at 8:36 am
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
November 28, 2012 at 8:44 am
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?
November 28, 2012 at 9:33 am
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
November 29, 2012 at 2:21 am
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.
November 29, 2012 at 2:28 am
you can make dynamic.
November 29, 2012 at 3:08 am
have got the desired result.. Thank you very much
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply