Cross Tab Query in SQL Server 2008.

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)

Cross Tab Query in SQL Server 2008.

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 below

Name Region Value
Kashif East 5
Josh West 4
Kashif North 87
Josh South 57
Mukesh West 27
Yogendra North 18
Mukesh South 64
Yogendra East 24

Now I want to create cross tab query that will produce result like below

Name East North South West
Josh 57 4
Kashif 5 87
Mukesh 64 27
Yogendra 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 55
Sujan North 87

Result should be like below.

Name East North South West
Josh 57 4
Kashif 5 87
Mukesh 64 27
Sujan 87 55
Yogendra 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 2005


SELECT Name,East, North, South, West
FROM Sales_Data s
PIVOT (SUM(Value) FOR Region IN (East, North, South, West))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 10:40:27

and to make columns also dynamic you can use below

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

i 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 MVP
http://visakhm.blogspot.com/

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 below

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

i 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 MVP
http://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 message

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '('.

SELECT *
FROM #temp123
PIVOT
(
SUM(VaribleValue)
FOR [Variable] IN ([Sales],[Expenses],[Taxes],[Profit])
)
AS p

I 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 above

run the query below and post the results


SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'your dbname'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 then


SELECT 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 #temp123
GROUP BY Country

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 then

SELECT 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 #temp123
GROUP BY Country

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi,

It's working now.

Many many thanks to you.

Thanks,
Kashif

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources