December 3, 2008 at 8:50 pm
Dear all,
I have a table which i have to pivot. I'm a beginner in this sort of stuff, so please help.
here is the table:
fullname team volume range
brian G1 1 0 to 100
randy G1 2 0 to 100
brian G1 3 101 to 200
brian G1 2 201 to 300
Zac G2 4 0 to 100
franc G2 3 101 to 200
dan G2 2 101 to 200
moz G3 2 0 to 100
ben G3 3 0 to 100
simon G3 2 101 to 200
i want it to be pivoted so it ends up like this:
volume
name 0 to 100 101 to 200 201 to 300
brian 1 3 2
randy 2 0 0
Zac 4 0 0
and so on.
Here is how i've written it which return to me with an error message saying incorrect syntax near '0 to 100'
select fullname,
'0 to 100,000','100,001 to 200,000','200,001 to 300,000','300,001 to 400,000','400,001 to 500,000','500,001 to 600,000','600,001 to 700,000',
'700,001 to 800,000','800,001 to 900,000','900,001 to 1,000,000','1,000,001+'
from
(select fullname,[range],volume from #ReportData) s
pivot
(
sum(volume) for [range] in ('0 to 100,000','100,001 to 200,000','200,001 to 300,000','300,001 to 400,000','400,001 to 500,000',
'500,001 to 600,000','600,001 to 700,000','700,001 to 800,000','800,001 to 900,000','900,001 to 1,000,000','1,000,001+')
) p
order by fullname
any idea of how to do it ?
thanks
December 4, 2008 at 1:21 am
justin you can use the below code this will help to avoid the hardcoded scopes.
IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL
DROP TABLE #tmpData
CREATE TABLE #tmpData(NameVARCHAR(10),
TeamVARCHAR(2),
VolumeSMALLINT,
ScopeVARCHAR(100)
)
INSERT#tmpData
SELECT'brian', 'G1', 1, '0 to 100' UNION ALL
SELECT'randy', 'G1', 2, '0 to 100' UNION ALL
SELECT'brian', 'G1', 3, '101 to 200' UNION ALL
SELECT'brian', 'G1', 2, '201 to 300' UNION ALL
SELECT'Zac', 'G2', 4, '0 to 100' UNION ALL
SELECT'franc', 'G2', 3, '101 to 200' UNION ALL
SELECT'dan', 'G2', 2, '101 to 200' UNION ALL
SELECT'moz', 'G3', 2, '0 to 100' UNION ALL
SELECT'ben', 'G3', 3, '0 to 100' UNION ALL
SELECT'simon', 'G3', 2, '101 to 200'UNION ALL
SELECT'john', 'G3', 2, '301 to 400'
DECLARE @vScopeVARCHAR(4000), @pvtScope VARCHAR(4000), @vsql VARCHAR(4000)
SELECT@vScope = COALESCE(@vScope + ', ', '') + '[' + Scope + ']',
@pvtScope = COALESCE(@pvtScope + ', ', '') + 'COALESCE([' + Scope + '], 0) AS '+ '[' + Scope + ']'
FROM(SELECT DISTINCT Scope FROM #tmpData) S
SET @vsql = 'SELECTName, ' + @pvtScope + CHAR(10)
+ 'FROM(SELECT Name, Volume, Scope FROM #tmpData) p '+ CHAR(10)
+ 'PIVOT(SUM(Volume) '+ CHAR(10)
+ 'FOR Scope IN( ' + @vScope+ CHAR(10)
+ ')) AS pvt '+ CHAR(10)
+ 'ORDER BY Name'
EXEC(@vSQL)
IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL
DROP TABLE #tmpData
Abhijit - http://abhijitmore.wordpress.com
December 4, 2008 at 7:13 am
[font="Verdana"]Try this ...
Create Table Pivot_Tab
(FullNameVarChar(10)
,TeamVarChar(2)
,VolumeInt
,RangeVarChar(15))
Go
Insert Into Pivot_Tab
Select 'brian', 'G1', 1, '0 to 100' Union All
Select 'randy', 'G1', 2, '0 to 100' Union All
Select 'brian', 'G1', 3, '101 to 200' Union All
Select 'brian', 'G1', 2, '201 to 300' Union All
Select 'Zac', 'G2', 4, '0 to 100' Union All
Select 'franc', 'G2', 3, '101 to 200' Union All
Select 'dan', 'G2', 2, '101 to 200' Union All
Select 'moz', 'G3', 2, '0 to 100' Union All
Select 'ben', 'G3', 3, '0 to 100' Union All
Select 'simon', 'G3', 2, '101 to 200'
Go
SelectFullName
,Team
,IsNull([0 to 100], 0) As [0 to 100]
,IsNull([101 to 200], 0) As [101 to 200]
,IsNull([201 to 300], 0) As [201 to 300]
FromPivot_Tab
Pivot
(
Sum(Volume)
For Range
In ([0 to 100], [101 to 200], [201 to 300])
) As Pvt
Order ByFullName
Go
Drop Table Pivot_Tab
Go
Mahesh[/font]
MH-09-AM-8694
December 4, 2008 at 4:24 pm
thank you very much.
it's doing what I want.
thank you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply