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.
Author |
Topic |
kashif.special2005
Starting Member
10 Posts |
Posted - 2012-04-30 : 10:36:56
|
Hi,I have table in sql server 2008 named “Sales_Data” like belowName Region ValueKashif East 5Josh West 4Kashif North 87Josh South 57Mukesh West 27Yogendra North 18Mukesh South 64Yogendra East 24Now I want to create cross tab query that will produce result like belowName East North South WestJosh 57 4Kashif 5 87 Mukesh 64 27Yogendra 24 18 If I add any data in table “Sales_Data” result would be automatically expand or reduce, like I two additional row in the table “Sales_Data”Sujan West 55Sujan North 87Result should be like below.Name East North South WestJosh 57 4Kashif 5 87 Mukesh 64 27Sujan 87 55Yogendra 24 18 I know it can be done through cross tab query, but I don’t know how to create cross tab query sql server 2008.Please help.Thanks,Kashif |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-30 : 10:39:05
|
its same as how you do in sql 2005SELECT Name,East, North, South, WestFROM Sales_Data sPIVOT (SUM(Value) FOR Region IN (East, North, South, West))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
kashif.special2005
Starting Member
10 Posts |
Posted - 2012-05-01 : 03:57:11
|
quote: Originally posted by visakh16 and to make columns also dynamic you can use belowhttp://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspxi dont think you'll need it in above case as i dont think you'll have more regions than these at any point------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hi,Thanks for reply,I have a table #temp123 with fields Country,Variable,VariableValue and I am using below syntax for cross tab query but it is giving me en error messageMsg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near '('.SELECT *FROM #temp123PIVOT( SUM(VaribleValue) FOR [Variable] IN ([Sales],[Expenses],[Taxes],[Profit]))AS pI am not able to understand that where is problem.Please help.Thanks,Kashif. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-01 : 10:07:29
|
are you using sql 2005 with compatibility level over 90? pivot works only from 90 and aboverun the query below and post the resultsSELECT @@VERSIONGOEXEC sp_dbcmptlevel 'your dbname' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-01 : 10:38:36
|
ok. thats the reason. you're using sql 2000 in which PIVOT is not available.use this instead thenSELECT Country,SUM(CASE WHEN [Variable] = 'Sales' THEN VaribleValue END) AS [Sales],SUM(CASE WHEN [Variable] = 'Expenses' THEN VaribleValue END) AS [Expenses],SUM(CASE WHEN [Variable] = 'Taxes' THEN VaribleValue END) AS [Taxes],SUM(CASE WHEN [Variable] = 'Profit' THEN VaribleValue END) AS [Profit]FROM #temp123GROUP BY Country ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
kashif.special2005
Starting Member
10 Posts |
Posted - 2012-05-01 : 10:43:49
|
quote: Originally posted by visakh16 ok. thats the reason. you're using sql 2000 in which PIVOT is not available.use this instead thenSELECT Country,SUM(CASE WHEN [Variable] = 'Sales' THEN VaribleValue END) AS [Sales],SUM(CASE WHEN [Variable] = 'Expenses' THEN VaribleValue END) AS [Expenses],SUM(CASE WHEN [Variable] = 'Taxes' THEN VaribleValue END) AS [Taxes],SUM(CASE WHEN [Variable] = 'Profit' THEN VaribleValue END) AS [Profit]FROM #temp123GROUP BY Country ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hi,It's working now.Many many thanks to you.Thanks,Kashif |
 |
|
|
|
|
|
|