Pivot Table - SQL Server Forums

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

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

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

indr4w
Starting Member

27 Posts

Posted - 2013-12-12 : 23:05:38

ok, solved. Thanks.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources