January 10, 2003 at 5:52 am
I have a table as below
cp_Name varchar(120)
MonthPeriod int(4)
Headroom money(8)
I want a result set giving the Headroomn value for each counterparty per monthly period as below...
MonthPeriod1 MonthPeriod2
cp_Name Headroom value Headroom Value
cp_Name Headroom value Headroom Value
...which is easy in MS Access but as i've been away from SQL for about 18 months it seems impossible. Can anyone help please...
Thanks...
January 10, 2003 at 6:06 am
In sql you do it like this but you really need to know how many columns you are pivoting on. ( I did post a script for unknown number of pivot columns, there are a few known issues with it I haven't updated yet but works for most cases.)
Based on your example, something like this.
SELECT
cp_name,
(Case when MonthPeriod = 1 then Headroom else NULL end) AS Month1,
(Case when MonthPeriod = 2 then Headroom else NULL end) AS Month2,
(Case when MonthPeriod = 3 then Headroom else NULL end) AS Month3,
...
FROM
tblName
GROUP BY
cp_name
January 10, 2003 at 6:14 am
Does month period correspond to 12 months integers 1 to 12? If so: -
select
cp_Name
,sum(case MonthPeriod when 1 then Headroom else 0 end) as MonthPeriod1
,sum(case MonthPeriod when 2 then Headroom else 0 end) as MonthPeriod2
,sum(case MonthPeriod when 3 then Headroom else 0 end) as MonthPeriod3
,sum(case MonthPeriod when 4 then Headroom else 0 end) as MonthPeriod4
,sum(case MonthPeriod when 5 then Headroom else 0 end) as MonthPeriod5
,sum(case MonthPeriod when 6 then Headroom else 0 end) as MonthPeriod6
,sum(case MonthPeriod when 7 then Headroom else 0 end) as MonthPeriod7
,sum(case MonthPeriod when 8 then Headroom else 0 end) as MonthPeriod8
,sum(case MonthPeriod when 9 then Headroom else 0 end) as MonthPeriod9
,sum(case MonthPeriod when 10 then Headroom else 0 end) as MonthPeriod10
,sum(case MonthPeriod when 11 then Headroom else 0 end) as MonthPeriod11
,sum(case MonthPeriod when 12 then Headroom else 0 end) as MonthPeriod12
from a
group by cp_Name
Regards,
Andy Jones
.
January 10, 2003 at 8:15 am
What you want is to perform a pivot table. Here are some examples:
http://www.geocities.com/sqlserverexamples/#pivot1
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 14, 2003 at 1:37 am
My problem is that there wil be an indeterminable number of columns. Antares686, Do you have a link to your script for an unknown number of pivot columns that you could post?
Much appreciated...
Rhys
January 14, 2003 at 1:37 am
My problem is that there wil be an indeterminable number of columns. Antares686, Do you have a link to your script for an unknown number of pivot columns that you could post?
Much appreciated...
Rhys
January 14, 2003 at 1:42 am
Hi, I think Greg's link in the above post will help.
Regards,
Andy Jones
.
January 16, 2003 at 8:12 am
I WROTE THE FOLLOWING DYNAMIC CROSS TAB FOR A STATE UNIVERSITY. THE PRINCIPLES WILL WORK WELL IN ALMOST ANY CASE
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure RollDateB @sd smalldatetime as
SET NOCOUNT ON
declare @I int
declare @sqlstr nvarchar(4000)
declare @test-2 as nvarchar(6)
--first Boca
set @I = 0
set @test-2 = replace(convert(nvarchar(6),@sd+@I,7),' ','_')
set @sqlstr = N'select classInstructor as Instructor,ClassNumber as [Class Number], ' + CHAR(13)
set @sqlstr = @sqlstr + 'sum(case when TranSubDate '
set @sqlstr = @sqlstr + ' <'''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,@I,@sd),101)
set @sqlstr = @sqlstr + ''' then 1 else 0 end) as [Prev Reg]'
set @sqlstr = @sqlstr + N','
set @sqlstr = @sqlstr + 'sum(case when TranSubDate '
set @sqlstr = @sqlstr + ' = '''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,@I,@sd),101)
set @sqlstr = @sqlstr + ''' then 1 else 0 end)'
set @sqlstr = @sqlstr + @test-2
while @I < 8
begin
set @I = @I + 1
set @test-2 = replace(convert(nvarchar(6),@sd+@I,7),' ','_')
set @sqlstr = @sqlstr + N','
set @sqlstr = @sqlstr + CHAR(13)+ 'sum(case when TranSubDate '
set @sqlstr = @sqlstr + ' = '''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,@I,@sd),101)
set @sqlstr = @sqlstr + ''' then 1 else 0 end) as '
set @sqlstr = @sqlstr + @test-2
end
set @sqlstr = @sqlstr + ', ' + CHAR(13) + ' count(*) as [Class Enroll] '
set @sqlstr = @sqlstr + + CHAR(13) + ' from reg...tblsubaccounting s
inner join reg...tblclasses c on
s.classid = c.classid
where SortOrder in (0,1) and TranSubDate between '''
set @sqlstr = @sqlstr + convert(nvarchar(10),getdate()-90,101)
set @sqlstr = @sqlstr + ''' AND '''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,45,@sd),101)
set @sqlstr = @sqlstr + '''and s.classid > 0 AND '
set @sqlstr = @sqlstr + ' AcctCategory not like ''Ref%'' and AcctCategory not like ''TXFR%''
group by ClassNumber,classinstructor'
--print @sqlstr
execute sp_executesql @sqlstr
--Now we go for the totals
set @I = 0
set @test-2 = replace(convert(nvarchar(6),@sd+@I,7),' ','_')
set @sqlstr = N'select '''',''Total'' as TOTAL, sum(case when TranSubDate '
set @sqlstr = @sqlstr + ' <'''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,@I,@sd),101)
set @sqlstr = @sqlstr + ''' then 1 else 0 end) as [Prev Reg]'
set @sqlstr = @sqlstr + N','
set @sqlstr = @sqlstr + 'sum(case when TranSubDate '
set @sqlstr = @sqlstr + ' = '''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,@I,@sd),101)
set @sqlstr = @sqlstr + ''' then 1 else 0 end) as '
set @sqlstr = @sqlstr + @test-2
while @I < 8
begin
set @I = @I + 1
set @test-2 = replace(convert(nvarchar(6),@sd+@I,7),' ','_')
set @sqlstr = @sqlstr + N','
set @sqlstr = @sqlstr + CHAR(13)+ 'sum(case when TranSubDate '
set @sqlstr = @sqlstr + ' = '''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,@I,@sd),101)
set @sqlstr = @sqlstr + ''' then 1 else 0 end) as '
set @sqlstr = @sqlstr + @test-2
end
set @sqlstr = @sqlstr + ', ' + CHAR(13) + ' count(*) as [Class Enroll] '
set @sqlstr = @sqlstr + + CHAR(13) + ' from reg...tblsubaccounting s
inner join reg...tblclasses c on
s.classid = c.classid
where SortOrder in (0,1) and TranSubDate between '''
set @sqlstr = @sqlstr + convert(nvarchar(10),getdate()-90,101)
set @sqlstr = @sqlstr + ''' AND '''
set @sqlstr = @sqlstr + convert(nvarchar(10),dateadd(d,45,@sd),101)
set @sqlstr = @sqlstr + '''and s.classid > 0 AND '
set @sqlstr = @sqlstr + ' AcctCategory not like ''Ref%'' and AcctCategory not like ''TXFR%'''
--print @sqlstr
execute sp_executesql @sqlstr
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
peter lundberg
peter lundberg
January 16, 2003 at 10:41 am
Here is the link http://www.sqlservercentral.com/scripts/contributions/204.asp
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply