| Author |
 Topic  |
|
|
patrickjao
Starting Member
Indonesia
24 Posts |
 Posted - 07/24/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 07/24/2012 : 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
India
47189 Posts |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 07/24/2012 : 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
Indonesia
24 Posts |
Posted - 07/25/2012 : 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
India
47189 Posts |
Posted - 07/25/2012 : 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
India
47189 Posts |
Posted - 07/25/2012 : 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
Indonesia
24 Posts |
Posted - 07/25/2012 : 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
Indonesia
24 Posts |
Posted - 07/25/2012 : 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
India
47189 Posts |
Posted - 07/25/2012 : 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
India
47189 Posts |
Posted - 07/25/2012 : 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
India
47189 Posts |
Posted - 07/25/2012 : 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/
|
|
|
| |
Topic |
|
crosstab query in sql
