在这种情况下如何使用PIVOT？ [英] How do I use PIVOT in this scenario ?

问题描述

伙计们我正在尝试为我的练习制作一个C＃win表单应用程序，它将生成支持票据。



我的数据库中有3个表： `tblTicketDetail`，`tblEngineer`和`tblTicket_Engineer`（`Junction table`用于多对多关系，只有两列`TicketID`和`EngineerID`分别来自`tblTicketDetail`和`tblEngineer` - 制作外键）。

生成新票后，每张票都分配给三位工程师。



现在我添加了另一张表格，其中我添加了DataGridView`将显示每张票的详细信息。



这个SQL查询正在填充DataGridView：

Guys I am trying to make a C# win form application for my practice, which is going to generate support tickets.

I have 3 tables in my database : `tblTicketDetail`, `tblEngineer` and `tblTicket_Engineer` (`Junction table` for many-to-many relationship having only two columns `TicketID` and `EngineerID` from `tblTicketDetail` and `tblEngineer` respectively- making foreign keys).
Upon generating a new ticket, each ticket is assigned to three engineers.

Now I have added another form in which I have added `DataGridView` which is going show each ticket's detail.

DataGridView is being populated by this SQL query :

SELECT dbo.tblTicketDetail.TicketID, dbo.tblTicketDetail.IssuerName,
dbo.tblEngineer.Name FROM  dbo.tblTicketDetail
INNER JOIN dbo.tblTicket_Engineer
ON dbo.tblTicketDetail.TicketID = dbo.tblTicket_Engineer.TicketID
INNER JOIN dbo.tblEngineer
ON dbo.tblTicket_Engineer.EngineerID = dbo.tblEngineer.EngineerID
WHERE (dbo.tblTicket_Engineer.TicketID = 1)";

现在我已经分配了1到3名工程师，所以`大taGridView`显示3行，每行有不同的工程师名称。哪个没关系，但我想要做的是，在`DataGridView`中添加3列，如`Engineer 1`，`Engineer 2`和`Engineer 3`，并用Engineer的名称填充它们，而不是显示3个不同的行（即每张票应该有一排）。现在我似乎无法弄清楚如何做到这一点。所以任何帮助都表示赞赏。



页码：

Now as I have assigned Ticket # 1 to 3 engineers, so the `DataGridView` shows up with 3 rows each having different engineer name. Which is okay but what i want to do is, Add 3 columns in `DataGridView` like `Engineer 1`, `Engineer 2` and `Engineer 3` and populate them with Engineer's name instead of showing 3 different rows (i.e. Each ticket should have one row). Now I can't seem to figure out how to do this. So any help is appreciated.

Page Code :

string strCon = ConfigurationManager.ConnectionStrings["ST"].
ConnectionString.ToString();
string strSQL = "//query mentioned above";
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strCon);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = bindingSource1;

图片供参考：



这是怎么回事上述查询返回结果：

http://i.stack.imgur.com/Cqemd。 png [ ^ ]



这就是我想要的结果（来自另一个包含所有这些列的表的图片）：

http://i.stack.imgur.com/rFHJL.png [ ^ ]



注意：每张票总共有3名工程师。他们的名字可能会改变，但总会有三名工程师。

Pictures for reference :

This is how the above query returns result :
http://i.stack.imgur.com/Cqemd.png[^]

This is how I want the result to be (picture from another table which has all these columns) :
http://i.stack.imgur.com/rFHJL.png[^]

Note : Each ticket will ALWAYS have 3 engineers. Their names may change but there will always be three engineers.

推荐答案

看看例子：

Have a look at example:

--TD
CREATE TABLE #tblTicketDetail
(
	TicketID INT,
	IssuerName NVARCHAR(30),
	TicketDescription NVARCHAR(30)
)


--EN
CREATE TABLE #tblEngineer
(
	EngineerID INT IDENTITY(1,1),
	[Name] NVARCHAR(30)
)

--TE
CREATE TABLE #tblTicket_Engineer
(
	TicketID INT,
	EngineerID INT
)


INSERT INTO #tblTicketDetail (TicketID, IssuerName, TicketDescription)
VALUES(1, 'Saqib', 'qwerty keyboard')

INSERT INTO #tblEngineer ([Name])
VALUES('Imran Khan'), ('Mubeen Khan'), ('Faraz Ahmed')

INSERT INTO #tblTicket_Engineer (TicketID, EngineerID)
VALUES(1,1),(1,2),(1,3)

SELECT  TicketID, IssuerName, TicketDescription, [1], [2], [3]
FROM (
	SELECT TD.TicketID, TD.IssuerName, TD.TicketDescription, ROW_NUMBER() OVER(ORDER BY TE.EngineerID) AS EngineerNo, EN.[Name]
	FROM  #tblTicketDetail AS TD
		INNER JOIN #tblTicket_Engineer AS TE ON TD.TicketID = TE.TicketID 
		INNER JOIN #tblEngineer AS EN ON TE.EngineerID = EN.EngineerID 
	WHERE (TE.TicketID = 1)
) AS DT
PIVOT(MAX([Name]) FOR [EngineerNo] IN([1], [2], [3])) AS PT

DROP TABLE #tblTicketDetail
DROP TABLE #tblEngineer
DROP TABLE #tblTicket_Engineer

结果：

Result:

Tic..ID Issu... TicketDesc...   1               2               3
1	Saqib	qwerty keyboard	Imran Khan	Mubeen Khan	Faraz Ahmed

这篇关于在这种情况下如何使用PIVOT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！