How convert SQL Server Pivot functionality in C# uisng LINQ
-
I am using SQL Server Pivot which works fine. Here is sample data below which transpose by SQL server pivot function. this is sample data which convert to pivot by SQL server.
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
| RowNumber | Section | LineItem | DisplayInCSM | Broker | BrokerName | ItemValue_NoFormat | Period |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
| 1 | Operational | NG Sales | NGL | CR | Credit Suse | 200 | 2010 FYA |
| 2 | Operational | NG Sales | NGL | GR | Max 1 | 300 | 2010 FYA |
| 3 | Operational | NG Sales | NGL | PX | Morgan | 100 | 2010 FYA |
| 4 | Operational | NG Sales | NGL | WB | Wells Fargo | 500 | 2010 FYA |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+This is dynamic sql i used in sql server to represent data in pivot format. here it is.
SET @SQL='SELECT *
FROM
(
SELECT RowNumber,CAST(ISNULL(EarningID,0) AS INT) EarningID,
Section,
LineItem,
DisplayInCSM,
Type,
Broker,
BrokerName,
ItemValue_NoFormat,
TRIM(ISNULL(Period,'''')) Period,hierarchy,
from #tmpData1 WHERE TYPE<>''SHEET''
) t
PIVOT
(
MAX(ItemValue_NoFormat)
FOR Broker IN ([5W], [8K], [CL], [DA], [EQ], [FA], [GS], [HM], [HQ], [JY], [KW], [ML], [MS], [MV], [SL], [UA],[WB])
) AS P
order by hierarchy,PeriodOrder -
I am using SQL Server Pivot which works fine. Here is sample data below which transpose by SQL server pivot function. this is sample data which convert to pivot by SQL server.
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
| RowNumber | Section | LineItem | DisplayInCSM | Broker | BrokerName | ItemValue_NoFormat | Period |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
| 1 | Operational | NG Sales | NGL | CR | Credit Suse | 200 | 2010 FYA |
| 2 | Operational | NG Sales | NGL | GR | Max 1 | 300 | 2010 FYA |
| 3 | Operational | NG Sales | NGL | PX | Morgan | 100 | 2010 FYA |
| 4 | Operational | NG Sales | NGL | WB | Wells Fargo | 500 | 2010 FYA |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+This is dynamic sql i used in sql server to represent data in pivot format. here it is.
SET @SQL='SELECT *
FROM
(
SELECT RowNumber,CAST(ISNULL(EarningID,0) AS INT) EarningID,
Section,
LineItem,
DisplayInCSM,
Type,
Broker,
BrokerName,
ItemValue_NoFormat,
TRIM(ISNULL(Period,'''')) Period,hierarchy,
from #tmpData1 WHERE TYPE<>''SHEET''
) t
PIVOT
(
MAX(ItemValue_NoFormat)
FOR Broker IN ([5W], [8K], [CL], [DA], [EQ], [FA], [GS], [HM], [HQ], [JY], [KW], [ML], [MS], [MV], [SL], [UA],[WB])
) AS P
order by hierarchy,PeriodOrderMou_kol wrote:
i saw these post Is it possible to Pivot data using LINQ?
Prolly is, but why move that to the client in the first place? Why do you prefer to hold a dataset in memory, when it is more efficient on the server?
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Mou_kol wrote:
i saw these post Is it possible to Pivot data using LINQ?
Prolly is, but why move that to the client in the first place? Why do you prefer to hold a dataset in memory, when it is more efficient on the server?
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
SQL server taking long time to return result set and we have network problem. also we do not have a DBA. using my knowledge i am not being able to suppress the issue. so decided to move the whole part in c#. so please guide me how to achieve it in c# alone. thanks
-
SQL server taking long time to return result set and we have network problem. also we do not have a DBA. using my knowledge i am not being able to suppress the issue. so decided to move the whole part in c#. so please guide me how to achieve it in c# alone. thanks
Mou_kol wrote:
SQL server taking long time to return result set and we have network problem. also we do not have a DBA
You missing DBA is prolly reason why it takes that time.
Mou_kol wrote:
using my knowledge i am not being able to suppress the issue
Welcome to my world.
Mou_kol wrote:
so decided to move the whole part in c#
Without knowing if that would improve anything?
Mou_kol wrote:
so please guide me how to achieve it in c# alone. thanks
No. Ethics. Not going to reccomend you wrong because you ask/want/need it. Get someone who knows databases. Profit that way.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
SQL server taking long time to return result set and we have network problem. also we do not have a DBA. using my knowledge i am not being able to suppress the issue. so decided to move the whole part in c#. so please guide me how to achieve it in c# alone. thanks
Your answer to meeting a deficiency is to ignore it and pursue a less efficient alternative. (You can look at it as a question or a statement).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
-
Your answer to meeting a deficiency is to ignore it and pursue a less efficient alternative. (You can look at it as a question or a statement).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
-
Damn. You brutal :D
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
SQL server taking long time to return result set and we have network problem. also we do not have a DBA. using my knowledge i am not being able to suppress the issue. so decided to move the whole part in c#. so please guide me how to achieve it in c# alone. thanks
This could mean anything. When you say that SQL Server is taking a long time to return the result set, you have left a wide open area here. Does the pivot table take a long time to create on the server? Is it returning a massive amount of data? Has anyone run a profiling session on the pivot operation to see if you are doing sequential scans? Doing an analysis in SQL Server does not need a DBA - the developers should be able to pick this up. In reality, I doubt you could build a pivot that would work as quickly as the SQL Server implementation which is heavily optimised. In order to pivot, you would have to pull all of the data back from the server then write your own code to transform the data, applying aggregation operations yourself, so you will need to potentially drag back large amounts of data. My advice - profile the PIVOT operation.
-
This could mean anything. When you say that SQL Server is taking a long time to return the result set, you have left a wide open area here. Does the pivot table take a long time to create on the server? Is it returning a massive amount of data? Has anyone run a profiling session on the pivot operation to see if you are doing sequential scans? Doing an analysis in SQL Server does not need a DBA - the developers should be able to pick this up. In reality, I doubt you could build a pivot that would work as quickly as the SQL Server implementation which is heavily optimised. In order to pivot, you would have to pull all of the data back from the server then write your own code to transform the data, applying aggregation operations yourself, so you will need to potentially drag back large amounts of data. My advice - profile the PIVOT operation.
:thumbsup:
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
