Need Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Query

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-19 : 02:13:31

I am having a table like this

Id--Name---Source---CreateDate
1---Wilson--WebSite-- 7/7/2012
2---jafry--Referal-- 7/7/2012
3---Bryce--WebSite-- 6/10/2012
4---Jim---WebSite--- 5/7/2012
5---Peter--WebSite-- 7/7/2011
6---John--Direct-- 7/7/2011

My req o/p

for year 2012
Source--May-- June-- July
Website-1-----1-------1
Referal-0-----0-------1
Direct-0-----0-------1

for year 2011
Source--May-- June-- July
Website-0-----0-------1
Referal-0-----0-------0
Direct-0-----0-------0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 02:26:19

use PIVOT operator. Please refer to BOL http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

KH
[spoiler]Time is always against us[/spoiler]

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-19 : 02:45:19

Thnak you Khtan.. I tried to create Pivot table like this

SELECT UtilityValue,
[Jan], [Feb], [Mar], [Apr], [May], [June], [July], [Aug], [Sep], [Oct], [Nov], [Dec]
FROM
(SELECT
DATEADD(mm,DATEDIFF(mm,0,create_date),0) AS FirstDayofCreatedMonth,
UtilityValue,marketing_source,count(isnull(marketing_source,0)) cnt
FROM
student join Utility on Utility.UID=student.marketing_source

GROUP BY
marketing_source,DATEADD(mm,DATEDIFF(mm,0,create_date),0),UtilityValue ) AS SourceTable
PIVOT
(
cnt
FOR month(FirstDayofCreatedMonth) IN ([Jan], [Feb], [Mar], [Apr], [May], [June], [July], [Aug], [Sep], [Oct], [Nov], [Dec])
) AS PivotTable;

but i got error as
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'FOR'.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-19 : 02:56:03

Thank you for your help.. i got it

SELECT *
FROM
(SELECT
DATEADD(mm,DATEDIFF(mm,0,create_date),0) AS FirstDayofCreatedMonth,
month(create_date) AS Mth,
UtilityValue,marketing_source
FROM
student join Utility on Utility.UID=student.marketing_source

GROUP BY
marketing_source,DATEADD(mm,DATEDIFF(mm,0,create_date),0),UtilityValue,create_date
) AS SourceTable
PIVOT
(
count(marketing_source)
FOR Mth IN ([1], [2], [3],[4],[5],[6],[7],

,[9],[10],[11] ,[12])
) AS PivotTable;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 02:56:27

[code]PIVOT
(
cnt
FOR month(FirstDayofCreatedMonth) IN ([Jan], [Feb], [Mar], [Apr], [May], [June], [July], [Aug], [Sep], [Oct], [Nov], [Dec])
) AS PivotTable;
[/code]

1. the cnt should be a aggregate function

2. this does not return month(FirstDayofCreatedMonth) the value Jan, Feb etc, it returns integer value 1 to 12

3. you can't do this month(FirstDayofCreatedMonth) in the PIVOT part. Do it in the SourceTable query
[code]
SELECT UtilityValue,
[Jan], [Feb], [Mar], [Apr], [May], [June], [July], [Aug], [Sep], [Oct], [Nov], [Dec]
FROM
(
SELECT
left(datename(month, create_date), 3) AS FirstDayofCreatedMonth,
UtilityValue,marketing_source
FROM
student join Utility on Utility.UID=student.marketing_source

) AS SourceTable
PIVOT
(
count(marketing_source)
FOR FirstDayofCreatedMonth IN ([Jan], [Feb], [Mar], [Apr], [May], [June], [July], [Aug], [Sep], [Oct], [Nov], [Dec])
) AS PivotTable;
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources