PIVOT -all columns will be null..how to solve?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » PIVOT -all columns will be null..how to...

PIVOT -all columns will be null..how to solve?

Rate Topic

Display Mode

Topic Options

Author

Message

vanapandi

Posted Wednesday, February 06, 2013 11:24 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 4:04 AM
Points: 22, Visits: 32

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);

Post #1416815

vinu512

Posted Thursday, February 07, 2013 4:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:37 AM
Points: 1,049, Visits: 1,439

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. Smile

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden Wink

Post #1416957

« Prev Topic | Next Topic »

Permissions