Hi! I have two parametrs in c#. How I can use it's in pivot table sql?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;
using System.Data.SqlClient;
namespace FormsReports
{
public partial class Form1 : Form
{
int iRowCnt = 0;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
int type = 0; string DateSal = "2018-09-04";
string constr = @"Server=.;DataBase=Test;UID=sa;PWD=d-123";
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"CREATE TABLE #FOOD(Id INT, NAME VARCHAR(10))
INSERT INTO #FOOD(Id, NAME)
SELECT 1,'apple' UNION ALL SELECT 2,'orange' UNION ALL SELECT 3,'tomato'
CREATE TABLE #PEOPLE(Id INT, NAME VARCHAR(10))
INSERT INTO #PEOPLE(Id, NAME)
SELECT 1,'Imomdod' UNION ALL SELECT 2,'Muhammad' UNION ALL SELECT 3,'Alisher'
CREATE TABLE #SALES(Id INT, IdPer INT, IDFOOD INT, price float, dateday date, typeprice int)
INSERT INTO #SALES(Id, IdPer, IDFOOD, price, dateday, typeprice)
SELECT 1,2,1, 3, '2018-09-04',0 UNION ALL SELECT 2,3,1, 5, '2018-09-04',0 UNION ALL SELECT 3,1,3, 11, '2018-09-04',1 UNION ALL
SELECT 4,3,2, 2, '2018-09-03',1 UNION ALL SELECT 5,2,1, 5, '2018-09-01',0 UNION ALL SELECT 6,1,2, 8, '2018-09-03',0 UNION ALL
SELECT 7,2,3, 4, '2018-09-04',0 UNION ALL SELECT 8,3,2, 10, '2018-09-02',1 UNION ALL SELECT 9,2,3, 15, '2018-09-04',1 UNION ALL
SELECT 10,1,2, 18, '2018-09-04',0 UNION ALL SELECT 11,3,3, 13, '2018-09-04',0 UNION ALL SELECT 12,1,3, 19, '2018-09-03',0
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(NAME) FROM (SELECT DISTINCT [NAME] FROM #FOOD) AS [NAME]
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
SET @DynamicPivotQuery = 'SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) Id
,PersonName,'+@ColumnName+'
,SUM('+@ColumnForSum+') Summa
FROM (
SELECT SUM(s.Price) Price,p.NAME as PersonName,f.NAME as FoodName
from #SALES s
INNER JOIN #PEOPLE p ON s.IdPer = p.Id
INNER JOIN #FOOD f ON s.IDFOOD = f.Id where typeprice = '+type+' and dateday = '+DateSal+'
GROUP BY p.Name,f.Name)t
pivot
(
MAX([Price]) FOR FoodName IN ('+@ColumnName+')
) piv GROUP BY PersonName,'+@ColumnName+''
EXEC (@DynamicPivotQuery)
DROP TABLE #SALES
DROP TABLE #FOOD
DROP TABLE #PEOPLE";
cmd.Connection = con;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
try
{
DataTable dt = new DataTable();
sda.Fill(dt);
Excel.Application xlAppToUpload = new Excel.Application();
xlAppToUpload.Workbooks.Add();
Excel.Worksheet xlWorkSheetToUpload = default(Excel.Worksheet);
xlWorkSheetToUpload = xlAppToUpload.Sheets["Лист1"];
// SHOW THE EXCEL SHEET.
// SETTING IT VISIBLE WILL ALLOW YOU TO SEE HOW IT WRITES DATA TO EACH CELL.
xlAppToUpload.Visible = true;
if (dt.Rows.Count > 0)
{
// Adding Header.
xlWorkSheetToUpload.Cells[3, 1].value = "Id";
xlWorkSheetToUpload.Cells[3, 2].value = "PersonName";
xlWorkSheetToUpload.Cells[3, 3].value = "apple";
xlWorkSheetToUpload.Cells[3, 4].value = "orange";
xlWorkSheetToUpload.Cells[3, 5].value = "tomato";
xlWorkSheetToUpload.Cells[3, 6].value = "Summa";
iRowCnt = 4; // ROW AT WHICH PRINT WILL START.
// NOW WRITE DATA TO EACH CELL.
for (var i = 0; i <= dt.Rows.Count - 1; i++)
{
xlWorkSheetToUpload.Cells[iRowCnt, 1].value = dt.Rows[i]["Id"];
xlWorkSheetToUpload.Cells[iRowCnt, 2].value = dt.Rows[i]["PersonName"];
xlWorkSheetToUpload.Cells[iRowCnt, 3].value = dt.Rows[i]["apple"];
xlWorkSheetToUpload.Cells[iRowCnt, 4].value = dt.Rows[i]["orange"];
xlWorkSheetToUpload.Cells[iRowCnt, 5].value = dt.Rows[i]["tomato"];
xlWorkSheetToUpload.Cells[iRowCnt, 6].value = dt.Rows[i]["Summa"];
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
xlAppToUpload.ActiveCell.Worksheet.Cells[4, 1].AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList2);
xlAppToUpload = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "You got an Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally { }
}
}
}
output result:
Id
|
PersonName
|
apple
|
orange
|
tomato
|
Summa
|
1
|
Alisher
|
5
|
|
13
|
18
|
2
|
Imomdod
|
|
18
|
|
18
|
3
|
Muhammad
|
3
|
|
4
|
7
|