Help me with a cross-tab - 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 2008 Forums

Transact-SQL (2008)

Help me with a cross-tab

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-03 : 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)

17689 Posts

Posted - 2012-12-03 : 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
[spoiler]Time is always against us[/spoiler]

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-04 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-04 : 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 - 2012-12-04 : 06:29:10

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-04 : 07:27:59