Need help to use Pivot - 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 2008 Forums

Transact-SQL (2008)

Need help to use Pivot

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-05-11 : 00:38:40

This is my table and some data,


CREATE TABLE [dbo].[collection](
	[paymentDte] [date] NULL,
	[codeDaerah] [smallint] NULL,
	[amt] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0x39340B00 AS Date), 1, CAST(70.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0x2F340B00 AS Date), 1, CAST(200.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0xC2330B00 AS Date), 4, CAST(600.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0x3A340B00 AS Date), 4, CAST(1000.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0x2C340B00 AS Date), 10, CAST(2910.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0xD1330B00 AS Date), 10, CAST(2275.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0xEC330B00 AS Date), 15, CAST(500.30 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0xA4350B00 AS Date), 1, CAST(700.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0x48360B00 AS Date), 2, CAST(400.00 AS Decimal(10, 2)))
INSERT [dbo].[collection] ([paymentDte], [codeDaerah], [amt]) VALUES (CAST(0x78360B00 AS Date), 1, CAST(7400.00 AS Decimal(10, 2)))
/****** Object:  Check [CK_collection_1]    Script Date: 05/11/2014 12:26:58 ******/
ALTER TABLE [dbo].[collection]  WITH CHECK ADD  CONSTRAINT [CK_collection_1] CHECK  ((datepart(year,[paymentDte])=(2011) OR datepart(year,[paymentDte])=(2012)))
GO
ALTER TABLE [dbo].[collection] CHECK CONSTRAINT [CK_collection_1]
GO
/****** Object:  Check [CK_collection_2]    Script Date: 05/11/2014 12:26:58 ******/
ALTER TABLE [dbo].[collection]  WITH CHECK ADD  CONSTRAINT [CK_collection_2] CHECK  (([codeDaerah]<=(16)))
GO
ALTER TABLE [dbo].[collection] CHECK CONSTRAINT [CK_collection_2]

My data should be as following,

My expected PIVOT shoud be as following



codeDaerah    | 2011       | 2012      
--------------------------------------------------------
1               270.00       8100.00        
4               1600.00      0.00        
10              5185.00      0.00      
15              500.30       0.00     
2               0.00         400.00

Please help

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-11 : 01:04:34

[code]

SELECT
codeDaerah
,COALESCE([2011],0) as [2011]
,COALESCE([2012],0) as [2012]
FROM
(
SELECT
amt
,codeDaerah
,year(paymentDte) as yearPayment
FROM [collection]
) C

pivot
(SUM(amt) FOR yearPayment in ([2011],[2012]))A
ORDER BY codeDaerah

[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-11 : 01:04:55

output:


codeDaerah	2011	2012
1	270.00	8100.00
2	0.00	400.00
4	1600.00	0.00
10	5185.00	0.00
15	500.30	0.00

sabinWeb MCP

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-05-11 : 01:25:25

tq sir :)

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-11 : 04:34:59

Your welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources