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

Author  Topic 

indr4w
Starting Member

27 Posts

Posted - 2013-12-11 : 02:54:41
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

52326 Posts

Posted - 2013-12-11 : 12:20:17
[code]
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
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 12:21:01
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

52326 Posts

Posted - 2013-12-11 : 12:43:55
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

27 Posts

Posted - 2013-12-12 : 23:05:38
ok, solved. Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-13 : 07:12:07
welcome

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

- Advertisement -