SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 crosstab query in sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 07/24/2012 :  07:21:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 07/24/2012 :  07:29:06  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/24/2012 :  09:50:58  Show Profile  Reply with Quote

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/

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/24/2012 :  09:58:56  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 07/25/2012 :  00:53:59  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/25/2012 :  01:07:01  Show Profile  Reply with Quote
whats your dbs compatibility level?
try below query and post the result


EXEC sp_dbcmptlevel 'Yourdatabasename'


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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/25/2012 :  01:08:50  Show Profile  Reply with Quote
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/

Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 07/25/2012 :  06:46:49  Show Profile  Reply with Quote
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/



Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 07/25/2012 :  07:58:30  Show Profile  Reply with Quote
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/





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/25/2012 :  10:30:56  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/25/2012 :  10:31:26  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/25/2012 :  10:34:54  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000