May 25, 2012 at 2:58 am
Hello
i have one table called
deposit
CREATE TABLE [dbo].[deposit](
[NominalID] [int] NULL,
[date] [datetime] NULL,
[Amount] [decimal](18, 2) NULL,
[flag] [varchar](2) NULL
) ON [PRIMARY]
Table has this data
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (83, CAST(0x0000A00700000000 AS DateTime), CAST(2650.00 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (58, CAST(0x0000A00800000000 AS DateTime), CAST(210.58 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (59, CAST(0x0000A00800000000 AS DateTime), CAST(2547.53 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (60, CAST(0x0000A00800000000 AS DateTime), CAST(0.00 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (61, CAST(0x0000A00800000000 AS DateTime), CAST(39.89 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (58, CAST(0x0000A00800000000 AS DateTime), CAST(101.43 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (59, CAST(0x0000A00800000000 AS DateTime), CAST(1313.76 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (60, CAST(0x0000A00800000000 AS DateTime), CAST(0.00 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (61, CAST(0x0000A00800000000 AS DateTime), CAST(8.31 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (83, CAST(0x0000A00800000000 AS DateTime), CAST(3200.00 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (83, CAST(0x0000A00900000000 AS DateTime), CAST(3590.00 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (83, CAST(0x0000A00A00000000 AS DateTime), CAST(1760.00 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (83, CAST(0x0000A00B00000000 AS DateTime), CAST(2720.00 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (35, CAST(0x0000A00C00000000 AS DateTime), CAST(1463.56 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (83, CAST(0x0000A00C00000000 AS DateTime), CAST(1286.44 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (62, CAST(0x0000A00800000000 AS DateTime), CAST(20.65 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (62, CAST(0x0000A00800000000 AS DateTime), CAST(4.74 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (83, CAST(0x0000A00D00000000 AS DateTime), CAST(2610.00 AS Decimal(18, 2)), N'B')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (103, CAST(0x0000A00C00000000 AS DateTime), CAST(529.82 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (104, CAST(0x0000A00C00000000 AS DateTime), CAST(1789.93 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (60, CAST(0x0000A00C00000000 AS DateTime), CAST(0.00 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (105, CAST(0x0000A00C00000000 AS DateTime), CAST(49.44 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (106, CAST(0x0000A00C00000000 AS DateTime), CAST(17.65 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (103, CAST(0x0000A00C00000000 AS DateTime), CAST(101.67 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (104, CAST(0x0000A00C00000000 AS DateTime), CAST(396.04 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (60, CAST(0x0000A00C00000000 AS DateTime), CAST(0.00 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (105, CAST(0x0000A00C00000000 AS DateTime), CAST(7.45 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (106, CAST(0x0000A00C00000000 AS DateTime), CAST(2.99 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (103, CAST(0x0000A00B00000000 AS DateTime), CAST(282.61 AS Decimal(18, 2)), N'Z')
INSERT [dbo].[deposit] ([NominalID], [date], [Amount], [flag]) VALUES (104, CAST(0x0000A00B00000000 AS DateTime), CAST(1830.39 AS Decimal(18, 2)), N'Z')
Now i am trying to select data from this table using pivot method of sql server
when i am trying this
SELECT nominalid, B,Z
FROM (
SELECT nominalid, Amount, Flag
FROM tbl_Deposit) up
PIVOT (SUM(Amount) FOR Flag IN (B, Z)) AS pvt
ORDER BY nominalid
its working fine
But when i try pivot data
based on date column
its not working
select dateadd(day,1,getdate())
select nominalid,@Date, dateadd(day,1,@Date)
from
(
select nominalid,amount,[date] from tbl_Deposit )up
PIVOT (sum(amount) for [date] in(@Date, dateadd(day,1,@Date)))as pvt
order by nominalid
Msg 102, Level 15, State 1, Procedure SP_Get_Weekly_BANK_Report, Line 45
Incorrect syntax near '@Date'.
Anyone please help
How i can achieve that...
I want like this
Nominalid 01/05/2012 02/05/2012
1 500 200
2 222 44
3 44 66
Please Suggest
May 25, 2012 at 4:17 am
hi ..
i solved my self
Declare @Date as DateTime = '03/01/2012'
Declare @Columns as Varchar(100) = '['+CAST(@Date as Varchar)+']' + ',' +'['+ CAST( dateadd(day,1,@Date) as Varchar) +']'
print @columns
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT nominalid,'+@Columns+'
FROM
(select nominalid,amount,[date] from tbl_Deposit )up
PIVOT
(
SUM(amount)
FOR [date]
IN (' + @Columns + ')
)
AS p order by nominalid'
print @query
EXECUTE(@query)
Thanks
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply