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 ««12

how to find result for this table Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 2, 2014 12:06 AM
Points: 22, Visits: 45

can 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..
Post #1415844
Posted Tuesday, February 5, 2013 6:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Select 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

DECLARE @ColStr VARCHAR(500),
@SqlStr VARCHAR(MAX);
-- Creating Column String
WITH col_str AS
(SELECT DISTINCT reason FROM Payment)
SELECT @ColStr=ISNULL(@ColStr+',','')+Reason FROM col_str;

-- Creating pivot query
SET @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 Query
EXEC (@SqlStr);



~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1416186
Posted Wednesday, February 6, 2013 1:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 2, 2014 12:06 AM
Points: 22, Visits: 45
Here 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 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 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);


Post #1416306
Posted Wednesday, February 6, 2013 9:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 2, 2014 12:06 AM
Points: 22, Visits: 45

Here 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 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 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);
Post #1416801
Posted Sunday, February 10, 2013 9:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 2, 2014 12:06 AM
Points: 22, Visits: 45

sir , i want to use '0' instead of null.here i couldn't use isNull().why?
Post #1418196
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse