| Author |
 Topic  |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
 Posted - 12/03/2012 : 19:00:15
|
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
16746 Posts |
Posted - 12/03/2012 : 22:58:56
|
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
|
 |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 12/04/2012 : 05:07:35
|
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. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/04/2012 : 05:29:45
|
try this...........
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + QUOTENAME([a].[Column])
FROM (SELECT DISTINCT right(MoYe,LEN(MoYe)-1) AS [Column]
FROM mytable) as a
--
Chandu |
|
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 12/04/2012 : 06:29:10
|
| bandi, that QUOTENAME function solved it. Works perfectly at least for now. Thanks! |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/04/2012 : 07:27:59
|
quote:
Originally posted by KilpAr
bandi, that QUOTENAME function solved it. Works perfectly at least for now. Thanks!
Welcome
--
Chandu |
|
|
| |
Topic |
|
Help me with a cross-tab
