February 6, 2013 at 11:24 pm
In PIVOT ,i couldn't find out ,,what things went wrong..anyone can solve this?
GO
/****** Object: Table [dbo].[paymentHistory] Script Date: 02/06/2013 13:56:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[paymentHistory](
[PaymentHistoryID] [int] IDENTITY(1,1) NOT NULL,
[paymentID] [int] NULL,
[receivedAmout] [money] NULL,
[receivedDate] [datetime] NULL,
[receivedType] [varchar](30) NULL,
[BankName] [varchar](100) NULL,
[Reason] [varchar](100) NULL,
[cheque_DD_no] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[paymentHistory] ON
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (1, 11, 5000.0000, CAST(0x0000A15900000000 AS DateTime), N'Dr', N'axis', N'ASD', N'12345')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (2, 12, 4000.0000, CAST(0x0000A15900000000 AS DateTime), N'Cr', N'axis', N' BG', N'12345')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (3, 11, 8000.0000, CAST(0x0000A15900000000 AS DateTime), N'Cr', N'axis', N' BG', N'12345')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (4, 11, 3000.0000, CAST(0x0000A15900000000 AS DateTime), N'Dr', N'axis', N' SD', N'12345')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (5, 11, 3000.0000, CAST(0x0000A15900000000 AS DateTime), N'Cr', N'axis', N'ASD', N'12345')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (6, 13, 50000.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'axis valasai', N'ASD', N'123456')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (7, 13, 50000.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'axis valasai', N'ASD', N'123456')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (8, 13, 1000.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'', N'ASD', N'')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (9, 13, 500.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'', N'ASD', N'')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (10, 14, 50000.0000, CAST(0x0000A15D00000000 AS DateTime), N'Cr', N'axis', N' FDR', N'54567')
SET IDENTITY_INSERT [dbo].[paymentHistory] OFF
-----my query--------------
declare @col_Str varchar(max) ,
@SQl_Query varchar(max);
WITH substitue as (Select Distinct Reason from paymentHistory )
select @col_Str=isnull(@col_Str+',','')+reason from substitue;
set @SQl_Query ='select paymentid,'+@col_Str+'
from (select paymentID, CASE receivedType when ''Dr'' then receivedAmout else -1* receivedAmout End receivedAmout ,Reason
from paymentHistory)as sourcetable
PIVOT( sum(receivedAmout) for reason IN ('+@col_Str+') ) as Pivota '
exec (@SQl_Query);
February 7, 2013 at 4:35 am
I hope this is what you require from the above data:
Declare @sql As Varchar(MAX)
Select @sql = 'Select paymentID, ' + STUFF((Select Distinct ',SUM(Case When Reason = ' + Char(39) + Reason + Char(39) + ' Then (Case When receivedtype = ''Dr'' Then ReceivedAmout Else -1*ReceivedAmout End) Else '''' End) As ' + Reason From paymenthistory FOR XML PATH('')),1,1,'') + ' From paymentHistory Group By paymentID '
print @sql
execute(@sql)
Test the code and see. Hope this helps. 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply