Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PIVOT -all columns will be null..how to solve? Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 11:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 2, 2014 12:06 AM
Points: 22, Visits: 45
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
Posted Thursday, February 7, 2013 4:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1416957
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse