dynamiv pivot in sql server

sourabh choubey

10y1.3k.NET

select * from
(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,Balance from dbo.CreditDebitExpenses inner join
dbo.CreditExpenses on dbo.CreditDebitExpenses.ID=dbo.CreditExpenses.Credit_ID
inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID )src
pivot
(
sum(Expenses)

for Expenses_Name In ([Electrical_Expenses],[Lift_Expenses],[BuildingMaintainence_Expenses],[Miscelleneous_Expenses])
)as pvt

i want [Electrical_Expenses],[Lift_Expenses],[BuildingMaintainence_Expenses],[Miscelleneous_Expenses] that is the records in expenses table having column name expenses_name is created dynamically no need to write as above..

23 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
I already given you modified line. Did you try that?
Also, please paste the output of "select Id, Expenses_name from dbo.Expenses"
Also, please paste the output of
declare @expenseNames nvarchar(max) = '';
select @expenseNames += case when datalength(@expenseName) = 0 then '' else ', ' end + '[' + ltrim(rtrim([Expense_name])) + ']'
from dbo.Expenses
print @expenseNames
0
sourabh choubey10y ago
hey listen when i print @ expensesname it print as
, [electric]
one more value is missing before comma .you see above what my output is when i print
@expensesname
modify this line only
select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ' ,'end + '['+ltrim(rtrim(Expenses_Name)) +']'
so that it gives correct output as
[buildingexpenses] , [electric]
0
Nitin Sontakke10y ago
Hello,
You will need to make up your mind what you want to do and how you want to do. You are completely confusing me now.
First I thought you are passing @expenseNames as a parameter then again you ask for the modifications in the code. I am giving it below, but I have no idea where you are putting it.
declare @dynamicSQL nvarchar(max) = '';
declare @expenseNames nvarchar(max) = ''
select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ' ,'end + '['+ltrim(rtrim(Expenses_Name)) +']'
from expenses
PRINT @expenseNames
0
sourabh choubey10y ago
hey nitin when i print@expenseNames
it shows output as , [eLECTRIC] instead of [buildingmaintainenece] ,[eLECTRIC]
please modify it...
declare @dynamicSQL nvarchar(max)
declare @expenseNames nvarchar(max)
select @expenseNames = case when datalength(@expenseNames) = 0 then '' else ' ,'end + '['+ltrim(rtrim(Expenses_Name)) +']'
from expenses
PRINT @expenseNames
0
sourabh choubey10y ago
is this sp right ....
USE [BMS]
GO
/****** Object: StoredProcedure [dbo].[sp_GetParticularsDetail] Script Date: 12-07-2016 18:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_GetParticularsDetail]
(
@expenseNames nvarchar(max)
)
as
begin
--select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'
--from expenses
declare @dynamicSQL nvarchar(max)='';
select @dynamicSQL =
'select * from
(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,
Balance from dbo.CreditDebitExpenses inner join
dbo.CreditExpenses on dbo.CreditDebitExpenses.ID=dbo.CreditExpenses.Credit_ID
inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID
)src
pivot
(
sum(Expenses)
for Expenses_Name In (' +@expenseNames+ ')
)as pvt'
end
0
sourabh choubey10y ago
IS IT RIGHT...
USE [BMS]
GO
/****** Object: StoredProcedure [dbo].[sp_GetParticularsDetail] Script Date: 12-07-2016 18:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_GetParticularsDetail]
(
@expenseNames nvarchar(max)
)
as
begin
select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'
from expenses
declare @dynamicSQL nvarchar(max)='';
select @dynamicSQL =
'select * from
(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,
Balance from dbo.CreditDebitExpenses inner join
dbo.CreditExpenses on dbo.CreditDebitExpenses.ID=dbo.CreditExpenses.Credit_ID
inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID
)src
pivot
(
sum(Expenses)
for Expenses_Name In (' +@expenseNames+ ')
)as pvt'
end
0
Nitin Sontakke10y ago
You need explain things a bit in advance.
I didn't expect you send @expenseNames as a parameter.
If you want that implementation, you need to add a parameter to the sp
Remove @expenseNames from the sp.
Make sure you always send proper values for @expenseNames.
alter stored procedure sp_GetParticularsDetail
(
@expenseNames nvarchar(max)
)
as
begin
....
end
While calling....
exec sp_GetParticularsDetail @expenseNames=N'Building_EXPENSES';
0
sourabh choubey10y ago
when i execute this query as
exec sp_GetParticularsDetail @expenseNames='Building_EXPENSES'
it will give error as
Procedure sp_GetParticularsDetail has no parameters and arguments were supplied.
0
sourabh choubey10y ago
I AM USING ENTITYFRAMEWORK .so WILL IT WORK...
0
Nitin Sontakke10y ago
I am sorry, Sourabh.
I don't think I understand what you are trying to say.
As far as I am thinking, if you execute the stored procedure in SQL Server and getting expected output there the SQL Server part is okay.
Can you please confirm that you are getting expected result if you execute sp in SQL Server itself.
For example:exec sp_GetParticularsDetail with appropriate parameters.
C# part is something different.
0
sourabh choubey10y ago
the query that i use return int value ok
public virtual int sp_GetParticularsDetail()
{
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("sp_GetParticularsDetail");
}
now when i call this
var particularsdetails = objparticulars.GetParticularsDetails();(in controller in my action)
it will return as -1
and when i check it as exec asexec sp_GetParticularsDetailit will give me the given value instead of the value that i origonally have
select * from
(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,
Balance from dbo.CreditDebitExpenses inner join
dbo.CreditExpenses on dbo.CreditDebitExpenses.ID=dbo.CreditExpenses.Credit_ID
inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID
)src
pivot
(
sum(Expenses)
for Expenses_Name In ([Building_EXPENSES], [eLECTRIC])
)as pvt
0

Nitin Sontakke10y ago

Sorry, I think I am confused.

What do you mean by "what to do with that string part."? It remains as is. We just execute it.

I am also not getting "it returns -1".

You should see the output just as before.

Are you getting error? If yes, please paste it here as it is.

If possible attach screen shot.

If possible, take the output of dynamic sql and execute it in another window.

sourabh choubey10y ago

hey when i execute it as it return as -1..

sourabh choubey10y ago

hey nitin now what to do with that string part.

Nitin Sontakke10y ago

That's the whole point. The entire query is first created as a string and then executed. That's what is dynamic sql.

sourabh choubey10y ago

declare @expenseNames nvarchar(max) = '';

select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'

from expenses

declare @dynamicSQL nvarchar(max)='';

select @dynamicSQL =

'select * from

(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebitExpenses inner join

dbo.CreditExpenses on dbo.CreditDebitExpenses.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID )src

pivot

(

sum(Expenses)

for Expenses_Name In (' +@expenseNames+ ')

)as pvt'

this is the code modified by u .The query executed successfully but can u say one thing why it become red from select to pvt when i use single quote

Nitin Sontakke10y ago

Hello,

If you are still getting error, please change double quotes to single quotes.

My mistake.

You can also do following:

print @dynamicSQL

and see that the query is correct and as expected.

sourabh choubey10y ago

declare @dynamicSQL nvarchar(max)=' ';

declare @expenseNames nvarchar(max) = ' ';

select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'

from expenses

select @dynamicSQL = "select * from

(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebitExpenses inner join

dbo.CreditExpenses on dbo.CreditDebitExpenses.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID )src

pivot

(

sum(Expenses)

for Expenses_Name In (' + @expenseNames + ')

)as pvt"

the datatype of Expenses_Name is varchar(250),null,

Nitin Sontakke10y ago

What is the data type of expense_name column in expenses table?

Also if you declare nvarchar(max) you should not get the length error at all.

Please post the query modified by you.

Also change the line as follows:

declare @expenseNames nvarchar(max) = '';

select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(expense_name)) + ']'

from expenses

sourabh choubey10y ago

hey thanx for give me the answer but still it has some error while i remove some of them like u declared @expenseNames but use '[' + expense_name + ']' and second one is (" + @expenseNames + ") ,the @expensesname inside as '+@expensesNames+'.I remove these two error .But the last error i am getting in"select * from.

the error i s

The identifier that starts with 'select * from

(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expen' is too long. Maximum length is 128.

how to declared its length more ....

Nitin Sontakke10y ago

In SQL Server....

declare @expenseNames nvarchar(max) = '';

select @expenseNames += case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + expense_name + ']'

from expenses

declare @dynamicSQL nvarchar(max) = '';

select @dynamicSQL = "select * from
(select dbo.CreditDebitExpenses.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,Balance from dbo.CreditDebitExpenses inner join
dbo.CreditExpenses on dbo.CreditDebitExpenses.ID=dbo.CreditExpenses.Credit_ID
inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID )src
pivot
(
sum(Expenses)

for Expenses_Name In (" + @expenseNames + ")
)as pvt"

sp_executeSQL @dynamicSQL

sourabh choubey10y ago

if u know nitin or any idea how to do this than say me..

Nitin Sontakke10y ago

It is very unfortunate that Microsoft has not really come out with a solution for this issue. It seem they are confused about what the end user might expect and this syntax.

When you want that list generated dynamically, unfortunately, the entire statement must be generated dynamically not just the part.

Hope you already know how to generate and execute a dynamic sql, if not, please ask.