SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help me with a cross-tab
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 12/03/2012 :  19:00:15  Show Profile  Reply with Quote
declare @SQLStr nvarchar(1024)

This kind of query, have clearly problems with MoYe (MonthYear) being in format of
'12/2012

What's the smart way to get around if we exclude saving them in different format like in date?


SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + [a].[Column]
FROM (SELECT DISTINCT right(MoYe,LEN(MoYe)-1) AS [Column]
FROM mytable) as a

SET @SQLStr = 'SELECT costcode, ' + @SQLStr
+ ' FROM (SELECT costcode, right(MoYe,LEN(MoYe)-1), summa FROM mytable) sq '
+ ' PIVOT (SUM(Summa) FOR MoYe IN ('
+ @SQLStr + ')) AS pt'

print @SQLStr
exec sp_executesql @SQLStr

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 12/03/2012 :  22:58:56  Show Profile  Reply with Quote
what are you trying to do with this ?
right(MoYe,LEN(MoYe)-1)


With your MoYe like 12/2012, that will give you 2/2012


KH
Time is always against us

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 12/04/2012 :  05:07:35  Show Profile  Reply with Quote
not really, since the months are in format of
'12/2012 i.e. results in 12/2012. The leading apostrophe is there due to some "brilliant" logic I used in Excel - if I get this Pivot working, I think I'll throw that part out too.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 12/04/2012 :  05:29:45  Show Profile  Reply with Quote
try this...........

SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + QUOTENAME([a].[Column])
FROM (SELECT DISTINCT right(MoYe,LEN(MoYe)-1) AS [Column]
FROM mytable) as a



--
Chandu
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 12/04/2012 :  06:29:10  Show Profile  Reply with Quote
bandi, that QUOTENAME function solved it. Works perfectly at least for now. Thanks!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 12/04/2012 :  07:27:59  Show Profile  Reply with Quote
quote:
Originally posted by KilpAr

bandi, that QUOTENAME function solved it. Works perfectly at least for now. Thanks!


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000