crosstab query in sql - 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)

crosstab query in sql

Author

Topic

patrickjao
Starting Member

24 Posts

Posted - 2012-07-24 : 07:21:44

I have table as follow :

Month,Type,Qty
1.......A......10
2.......B.......5
3.......C.......6
4.......B.......3

How to change it to

Type 1..2..3..4
A.....10.0..0..0
B.....0..5..0..3
C.....0..0..6..0

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-24 : 07:29:06

Look for PIVOT in books online or google it.
There are many examples.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 09:50:58

[code]
SELECT *
FROM Table
PIVOT (SUM(Qty) FOR [Month] IN ([1],[2],[3],[4]))p
[/code]

also see how you can include totals in it if you want

http://visakhm.blogspot.com/2012/04/display-total-rows-with-pivotting-in-t.html

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-24 : 09:58:56

or better yet, Don't do it in the db at all.

What presentation layer are you using? It's easier there. Db is good at getting your results. If you can, don't ask it to do formatting as well.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

patrickjao
Starting Member

24 Posts

Posted - 2012-07-25 : 00:53:59

Thank you visakhm, I give may table name TEST
when I run the following script
SELECT TOP 100 PERCENT [MONTH]
,[TYPE]
,[QTY]
FROM [TEST]
is work well

but once I add

PIVOT (SUM(Qty) FOR [Month] IN ([1],[2],[3],[4]))p

always return
Incorrect syntax near '('.

Please advice why?? NB :I am using SQLSERVER 2008

quote:
Originally posted by visakh16

SELECT *
FROM Table
PIVOT (SUM(Qty) FOR [Month] IN ([1],[2],[3],[4]))p

also see how you can include totals in it if you want

http://visakhm.blogspot.com/2012/04/display-total-rows-with-pivotting-in-t.html

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 01:07:01

whats your dbs compatibility level?
try below query and post the result


EXEC sp_dbcmptlevel 'Yourdatabasename'

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 01:08:50

in any case below should work


SELECT [TYPE]
,SUM(CASE WHEN [MONTH] = 1 THEN [QTY] ELSE 0 END) AS [1],
,SUM(CASE WHEN [MONTH] = 2 THEN [QTY] ELSE 0 END) AS [2],
,SUM(CASE WHEN [MONTH] = 3 THEN [QTY] ELSE 0 END) AS [3],
,SUM(CASE WHEN [MONTH] = 4 THEN [QTY] ELSE 0 END) AS [4]
FROM [TEST]
GROUP BY [TYPE]

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

patrickjao
Starting Member

24 Posts

Posted - 2012-07-25 : 06:46:49

Thank you very much, the test result as follow:

The current compatibility level is 80.

quote:
Originally posted by visakh16

whats your dbs compatibility level?
try below query and post the result

EXEC sp_dbcmptlevel 'Yourdatabasename'

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

patrickjao
Starting Member

24 Posts

Posted - 2012-07-25 : 07:58:30

Thank you very2 much, you are really great, is my db version problem, my data server is using SQL 2000, my computer using SQL 2008.
I have create the same database in my computer and run the query, it work!!!!
any possiblility to run it in sql2000? how to do it?

or is there possible to make table (use database Agent/scheduler) from server name A(sql2000) database A1 to server name B(Sql2008 different computer) database B1? if yes, please give me a simple make table query example,it will also solve my problems, I can run the pivot query in sql2008 server.

Thanks and Regards

quote:
Originally posted by patrickjao

Thank you very much, the test result as follow:

The current compatibility level is 80.

quote:
Originally posted by visakh16

whats your dbs compatibility level?
try below query and post the result

EXEC sp_dbcmptlevel 'Yourdatabasename'

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:30:56

quote:
Originally posted by patrickjao

Thank you very2 much, you are really great, is my db version problem, my data server is using SQL 2000, my computer using SQL 2008.
I have create the same database in my computer and run the query, it work!!!!
any possiblility to run it in sql2000? how to do it?

or is there possible to make table (use database Agent/scheduler) from server name A(sql2000) database A1 to server name B(Sql2008 different computer) database B1? if yes, please give me a simple make table query example,it will also solve my problems, I can run the pivot query in sql2008 server.

Thanks and Regards

quote:
Originally posted by patrickjao

Thank you very much, the test result as follow:

The current compatibility level is 80.

quote:
Originally posted by visakh16

whats your dbs compatibility level?
try below query and post the result

EXEC sp_dbcmptlevel 'Yourdatabasename'

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

see my last posted suggestion. it will work in sql 2000 also

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:31:26

quote:
Originally posted by patrickjao

Thank you very much, the test result as follow:

The current compatibility level is 80.

quote:
Originally posted by visakh16

whats your dbs compatibility level?
try below query and post the result

EXEC sp_dbcmptlevel 'Yourdatabasename'

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

PIVOT works only from compatibility level 90 onwards

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:34:54

quote:
Originally posted by patrickjao

Thank you very2 much, you are really great, is my db version problem, my data server is using SQL 2000, my computer using SQL 2008.
I have create the same database in my computer and run the query, it work!!!!
any possiblility to run it in sql2000? how to do it?

or is there possible to make table (use database Agent/scheduler) from server name A(sql2000) database A1 to server name B(Sql2008 different computer) database B1? if yes, please give me a simple make table query example,it will also solve my problems, I can run the pivot query in sql2008 server.

Thanks and Regards

quote:
Originally posted by patrickjao

Thank you very much, the test result as follow:

The current compatibility level is 80.

quote:
Originally posted by visakh16

whats your dbs compatibility level?
try below query and post the result

EXEC sp_dbcmptlevel 'Yourdatabasename'

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

easiest way to create table in new server is to script out table from sql 2000 server and apply it in sql 2008 along with constraints,indexes etc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources