August 27, 2010 at 10:38 am
sqlbi (8/27/2010)
It doesn't help basically i want it to be in.. wether im doing mistake some where in the script?2008-022008-032008-042008-052008-06
A 123 234 132 245 300
B 1235 2546 541 1234 457
C 1457 1456 545 145 2546
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
You've been asked for this information several times. Until you provide it, you're not getting any more help from me. I cannot afford to waste my time continually guessing at what your issue is. Now, if you want to send me via PayPal $250 USD, I'll be happy to waste up to 3 hours helping you out.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 27, 2010 at 10:51 am
WayneS (8/27/2010)
... Now, if you want to send me via PayPal $250, I'll be happy to waste up to 3 hours helping you out.
😀 SOM 😀 Gorgeous!
In fact, considering the current exchange rate and the quality of work someone could expect, this sounds like a fair price... I'm gonna keep that post (just in case...) 😉
August 27, 2010 at 11:23 am
im sorry for my pervious posts here is what i tried
CREATE TABLE [dbo].[tblsample](
[EmailAddrID] [int] NOT NULL,
[SourceCode] [varchar](4) NOT NULL,
[RegisterTimestamp] [varchar](8) NOT NULL
)
--Data
Insert into tblsample
values('1023027','A','2009-12')
Insert into tblsample
values('1023027','A','2009-12')
Insert into tblsample
values('10230','B','2009-11')
Insert into tblsample
values('10230','B','2009-11')
------Query I Used----------------
with CTETemp
as(
Select CASE
When SourceCode = 'A' Then 'ResultOne'
When SourceCode = 'B' Then 'ResultTwo'
Else 'Other'
End as Sorce,
RegisterTimestamp,
COUNT(EmailAddrID) As CountEntery
From tblsample
Group By RegisterTimestamp,
Case When SourceCode = 'A' Then 'ResultOne'
When SourceCode = 'B' Then 'ResultTwo'
Else 'Other'
End
)
SELECT * FROM (SELECT Sorce,RegisterTimestamp, CountEntery FROM CTETemp) AS a
PIVOT(COUNT(Emailaddrid) FOR sorce IN (2009-12,2009-11)) AS p
--Error
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near '2003'.
---Output Should be
sorce 2009-12 2009-11
Resultone 2 2
ResultTwo 2 2
August 27, 2010 at 11:29 am
First, your error message is wrong for your new code. You should be getting 2 errors, not just the 2003 error. And it should be 2009, not 2003, based on the sample data you just gave us.
Secondly, You didn't include the square brackets REQUIRED in the PIVOT statement. Go back and look at the sample code. Your final IN statement.
Thirdly, the PIVOT is trying to count a column that doesn't exist in your CTE.
August 27, 2010 at 11:36 am
LutzM (8/27/2010)
WayneS (8/27/2010)
... Now, if you want to send me via PayPal $250, I'll be happy to waste up to 3 hours helping you out.😀 SOM 😀 Gorgeous!
In fact, considering the current exchange rate and the quality of work someone could expect, this sounds like a fair price... I'm gonna keep that post (just in case...) 😉
Wayne has offered a discount rate - wow 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 27, 2010 at 11:48 am
First, your error message is wrong for your new code. You should be getting 2 errors, not just the 2003 error. And it should be 2009, not 2003, based on the sample data you just gave us.
Secondly, You didn't include the square brackets REQUIRED in the PIVOT statement. Go back and look at the sample code. Your final IN statement.
Thirdly, the PIVOT is trying to count a column that doesn't exist in your CTE.
---------------------------------------------------------------------------------------------------------------------------
i changed query as you mentioned above but it is saying invalid emailaddrid
with CTETemp
as(
Select CASE
When SourceCode = 'A' Then 'ResultOne'
When SourceCode = 'B' Then 'ResultTwo'
Else 'Other'
End as Sorce,
RegisterTimestamp,EmailAddrid
COUNT(EmailAddrID) As CountEntery
From tblsample
Group By RegisterTimestamp,
Case When SourceCode = 'A' Then 'ResultOne'
When SourceCode = 'B' Then 'ResultTwo'
Else 'Other'
End
)
SELECT * FROM (SELECT Sorce,RegisterTimestamp, CountEntery FROM CTETemp) AS a
PIVOT(COUNT(Emailaddrid) FOR sorce IN ([2009-12],[2009-11])) AS p
August 27, 2010 at 12:07 pm
sqlbi (8/27/2010)
Thirdly, the PIVOT is trying to count a column that doesn't exist in your CTE.---------------------------------------------------------------------------------------------------------------------------
i changed query as you mentioned above but it is saying invalid emailaddrid
with CTETemp
as(
Select CASE
When SourceCode = 'A' Then 'ResultOne'
When SourceCode = 'B' Then 'ResultTwo'
Else 'Other'
End as Sorce,
RegisterTimestamp,EmailAddrid
COUNT(EmailAddrID) As CountEntery
From tblsample
Group By RegisterTimestamp,
Case When SourceCode = 'A' Then 'ResultOne'
When SourceCode = 'B' Then 'ResultTwo'
Else 'Other'
End
)
SELECT * FROM (SELECT Sorce,RegisterTimestamp, CountEntery FROM CTETemp) AS a
PIVOT(COUNT(Emailaddrid) FOR sorce IN ([2009-12],[2009-11])) AS p
That's what I mean by "You're trying to count a column that doesn't exist in your CTE." Look at all your column names. Aliases overwrite column names. Look carefully.
August 27, 2010 at 1:00 pm
Thanks for your help i solved the issue..
here im entering dates manaully and i dont want to do that.. is there any solution to solve it?
August 27, 2010 at 3:21 pm
sqlbi (8/27/2010)
Thanks for your help i solved the issue..here im entering dates manaully and i dont want to do that.. is there any solution to solve it?
Yes. Dynamic pivot table. See the Part 2 Pivot/Crosstab link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply