InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 09:37:34 GMT20http://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxsir , i want to use '0' instead of null.here i couldn't use isNull().why?Sun, 10 Feb 2013 21:43:58 GMTvanapandihttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxHere i got some kind of error..all columns filled with Null...why?.I couldn't find what..please help me sir,,,/****** Object: Table [dbo].[paymentHistory] Script Date: 02/06/2013 13:56:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[paymentHistory] ONINSERT [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 col as (Select Distinct Reason from paymentHistory )select @col_Str=isnull(@col_Str+',','')+reason from col;set @SQl_Query ='select paymentid,'+@col_Str+'from (select paymentID, CASE receivedType when ''Dr'' then receivedAmout else -1* receivedAmout End receivedAmout ,Reasonfrom paymentHistory)as sourcetablePIVOT( sum(receivedAmout) for reason IN ('+@col_Str+') ) as Pivota 'exec (@SQl_Query);Wed, 06 Feb 2013 21:35:37 GMTvanapandihttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxHere i got some kind of error..all columns filled with Null...why?.I couldn't find what..please help me sir,,,/****** Object: Table [dbo].[paymentHistory] Script Date: 02/06/2013 13:56:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[paymentHistory] ONINSERT [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 col as (Select Distinct Reason from paymentHistory ) select @col_Str=isnull(@col_Str+',','')+reason from col;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); Wed, 06 Feb 2013 01:35:30 GMTvanapandihttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxSelect statement cannot be used directly to create headers. Here is how it can be done by creating a list of columns and then creating a PIVOT string which include those columns[code="sql"]DECLARE @ColStr VARCHAR(500), @SqlStr VARCHAR(MAX); -- Creating Column StringWITH col_str AS(SELECT DISTINCT reason FROM Payment)SELECT @ColStr=ISNULL(@ColStr+',','')+Reason FROM col_str;-- Creating pivot querySET @SqlStr ='SELECT paymentID, '+@ColStr+'FROM (SELECT paymentID, CASE receivedType WHEN ''Dr'' THEN Amount ELSE -1 * Amount END Amount, Reason FROM Payment) AS SourceTable PIVOT ( Sum(Amount) FOR Reason IN ('+@ColStr+') ) AS PivotTable '-- Execute QueryEXEC (@SqlStr);[/code]Tue, 05 Feb 2013 18:02:50 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxcan i create dynamic headers....:-)SELECT paymentID, [ASD], [BG],[SD]FROM (SELECT paymentID, CASE receivedType WHEN 'Dr' THEN Amount ELSE -1 * Amount END Amount, Reason FROM Payment) AS SourceTable PIVOT ( Sum(Amount) FOR Reason IN ([ASD], [BG], [SD]) ) AS PivotTable; //Here can i put SELECT statement for Dynamic Headers..Tue, 05 Feb 2013 07:43:03 GMTvanapandihttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxThis is how it can be done, using PIVOT function[code="sql"]SELECT paymentID, [ASD], [BG],[SD]FROM (SELECT paymentID, CASE receivedType WHEN 'Dr' THEN Amount ELSE -1 * Amount END Amount, Reason FROM Payment) AS SourceTable PIVOT ( Sum(Amount) FOR Reason IN ([ASD], [BG], [SD]) ) AS PivotTable; [/code]Tue, 05 Feb 2013 06:47:24 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxTry to do this yourself. Here is the link for your reference:[url]http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[/url]Tue, 05 Feb 2013 02:29:57 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxCREATE TABLE Payment ( PaymentHistoryID INT, paymentID INT, Amount DECIMAL(10, 2), receivedType CHAR(2), Reason VARCHAR(10) ); INSERT INTO PaymentVALUES (1,11, 5000.00,'Dr','ASD'),(2,12, 4000.00,'Cr', 'BG'),(3,11, 8000.00,'Cr', 'BG'),(4,11, 3000.00,'Dr', 'SD'),(5,11, 3000.00,'Cr','ASD'),(6,13,50000.00,'Cr','ASD'),(7,13,50000.00,'Cr','ASD'),(8,13, 1000.00,'Cr','ASD'),(9,13, 500.00,'Cr','ASD')Now I got another problem..i need result like that..paymentID SD ASD BG-----------------------------------11 -3000 -2000 800012 0 0 4000is it possible ...Tue, 05 Feb 2013 02:05:50 GMTvanapandihttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspx[quote][b]vanapandi (2/5/2013)[/b][hr]thank you sir,,,i got result...i want to improve my knowledge in sql server..can you suggest me..i am also finally got result by using sub query...whether it is wrong way select distinct cr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0'),Dr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),Amount=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0')-isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),Reason from paymentHistory ph group by Reason[/quote]I don't think that query executes , does it ?<< For improving the knowledge , you can start with reading msdn articles and experiment a lot ; that would give you some ideas..>>Tue, 05 Feb 2013 01:17:49 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxthank you sir,,,i got result...i want to improve my knowledge in sql server..can you suggest me..i am also finally got result by using sub query...whether it is wrong way select distinct cr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0'),Dr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),Amount=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0')-isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),Reason from paymentHistory ph group by ReasonTue, 05 Feb 2013 01:09:33 GMTvanapandihttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspx[quote][b]demonfox (2/4/2013)[/b][hr]And the best part ; Float hasa higher precedence over int , so multiplying Amount with -1 doesn't convert the data type of amount ...[/quote]Hey demonfox..spot on with regards to today's Qotd :-)Tue, 05 Feb 2013 00:28:22 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspx[quote][b]Lokesh Vij (2/4/2013)[/b][hr]Here you go:[code="sql"]CREATE TABLE Payment ( PaymentHistoryID INT, paymentID INT, Amount DECIMAL(10, 2), receivedType CHAR(2), Reason VARCHAR(10) ); INSERT INTO PaymentVALUES (1,11, 5000.00,'Dr','ASD'),(2,12, 4000.00,'Cr', 'BG'),(3,11, 8000.00,'Cr', 'BG'),(4,11, 3000.00,'Dr', 'SD'),(5,11, 3000.00,'Cr','ASD'),(6,13,50000.00,'Cr','ASD'),(7,13,50000.00,'Cr','ASD'),(8,13, 1000.00,'Cr','ASD'),(9,13, 500.00,'Cr','ASD')SELECT paymentID, Sum(CASE receivedType WHEN 'Dr' THEN Amount ELSE -1 * Amount END) Amount, ReasonFROM PaymentGROUP BY paymentID, Reason [/code][quote][b]demonfox (2/4/2013)[/b][hr]Please provide the ddl ..follow this link for best practices to post :http://www.sqlservercentral.com/articles/Best+Practices/61537/[/quote]Always provide DDL along with test data and expected result, as mentioned by demonfox...[/quote]And the best part ; Float hasa higher precedence over int , so multiplying Amount with -1 doesn't convert the data type of amount ...Mon, 04 Feb 2013 23:16:32 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxHere you go:[code="sql"]CREATE TABLE Payment ( PaymentHistoryID INT, paymentID INT, Amount DECIMAL(10, 2), receivedType CHAR(2), Reason VARCHAR(10) ); INSERT INTO PaymentVALUES (1,11, 5000.00,'Dr','ASD'),(2,12, 4000.00,'Cr', 'BG'),(3,11, 8000.00,'Cr', 'BG'),(4,11, 3000.00,'Dr', 'SD'),(5,11, 3000.00,'Cr','ASD'),(6,13,50000.00,'Cr','ASD'),(7,13,50000.00,'Cr','ASD'),(8,13, 1000.00,'Cr','ASD'),(9,13, 500.00,'Cr','ASD')SELECT paymentID, Sum(CASE receivedType WHEN 'Dr' THEN Amount ELSE -1 * Amount END) Amount, ReasonFROM PaymentGROUP BY paymentID, Reason [/code][quote][b]demonfox (2/4/2013)[/b][hr]Please provide the ddl ..follow this link for best practices to post :http://www.sqlservercentral.com/articles/Best+Practices/61537/[/quote]Always provide DDL along with test data and expected result, as mentioned by demonfox...Mon, 04 Feb 2013 23:00:16 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxPlease provide the ddl ..follow this link for best practices to post :http://www.sqlservercentral.com/articles/Best+Practices/61537/Mon, 04 Feb 2013 22:41:29 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1415570-391-1.aspxPaymentHistoryID paymentID Amount receivedType Reason1 11 5000.00 Dr ASD2 12 4000.00 Cr BG3 11 8000.00 Cr BG4 11 3000.00 Dr SD5 11 3000.00 Cr ASD6 13 50000.00 Cr ASD7 13 50000.00 Cr ASD8 13 1000.00 Cr ASD9 13 500.00 Cr ASDThis is my table………..pls help me at vanapandi@gmail.comPayment ID Amount(dr-cr) Reason11 5000-3000=2000 ASD11 0-8000=-8000 BG12 0-4000=-4000 BG I need result like below table…how to write query for getting this table.Mon, 04 Feb 2013 22:26:50 GMTvanapandi