Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Pivot Table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indr4w
Starting Member

Indonesia
27 Posts

Posted - 12/11/2013 :  02:54:41  Show Profile  Reply with Quote
Hello,

I have the following view structure

Notrans varchar 30
novoucher varchar 30
date datetime
nominal money
bank_name varchar 30
month varchar 4
year varchar 4

idtrans voucherNo date Nominal bank_name month Year
001 KM001 01/12/2013 1000000 PANIN 220 12 2013
002 KM002 01/12/2013 1000000 MANDIRI 382 12 2013
003 KM003 02/12/2013 3000000 PANIN 220 12 2013
004 KM004 02/12/2013 1000000 MANDIRI 382 12 2013
040 KM040 31/12/2013 3200000 PANIN 220 12 2013
041 KM041 31/12/2013 2100000 MANDIRI 382 12 2013

how to create sql script as below

date PANIN 220 MANDIRI 382
1 1000000 2000000
2 3000000 2200000
...
31 3200000 2100000

Thank's

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/11/2013 :  12:20:17  Show Profile  Reply with Quote

SELECT *
FROM
(
SELECT f.[date],t.bank_name,Nominal
FROM dbo.CalendarTable('20131201','20131231')f
LEFT JOIN YourTable t
On t.[date] = f.[date]
)m
PIVOT(SUM(Nominal) FOR bank_name IN ([PANIN 220],[MANDIRI 382]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/11/2013 :  12:21:01  Show Profile  Reply with Quote
To make it dynamic see
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/11/2013 :  12:43:55  Show Profile  Reply with Quote
just noticed you've posted it in sql 2000 forum.
In sql 200 pivot wont work so you've to use below method


SELECT f.[date],
MAX(CASE WHEN t.bank_name = 'PANIN 220' THEN Nominal END) AS [PANIN 220],
MAX(CASE WHEN t.bank_name = 'MANDIRI 382' THEN Nominal END) AS [MANDIRI 382],
...
FROM dbo.CalendarTable('20131201','20131231')f
LEFT JOIN YourTable t
On t.[date] = f.[date]
GROUP BY f.[date]


and to make it dynamic use below


http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 12/12/2013 :  23:05:38  Show Profile  Reply with Quote
ok, solved. Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/13/2013 :  07:12:07  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next 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.05 seconds. Powered By: Snitz Forums 2000