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 2000 Forums
 Transact-SQL (2000)
 Pivot Table
 New Topic  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
52317 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
52317 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
52317 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
52317 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  
 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