Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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 :

SQL
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)";


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 :

C#
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;


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.
Posted
Comments
Schatak 29-Mar-14 7:39am    
can you please tell me your table structure as well?
Shajee Afzal 29-Mar-14 8:12am    
tblEngineer has 2 columns : EngineerID(PK) and Name
tblTicketDetail has 3 columns : TicketID(PK) and IssuerName & TicketDescripton
tblTicket_Engineer has 3 columns : PID(PK), TicketID(FK) and EngineerID(FK) --- Junction table
Maciej Los 29-Mar-14 15:53pm    
Instead posting link to the pictures, please, post the structure of tables and samples data. Use "Improve question" widget.

1 solution

Have a look at example:

SQL
--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:
SQL
Tic..ID Issu... TicketDesc...   1               2               3
1	Saqib	qwerty keyboard	Imran Khan	Mubeen Khan	Faraz Ahmed
 
Share this answer
 
Comments
Shajee Afzal 31-Mar-14 0:40am    
@losmac
Thank you sir. You have been most helpful. This was driving me crazy all week. Just one problem, if I remove the WHERE clause from the statement, I get nulls in "1, 2 and 3" columns (There are no nulls in either tables). I am just trying to display all the tickets generated.
Picture for reference :
http://s28.postimg.org/wrldv2fp9/pivot.png
Maciej Los 31-Mar-14 2:30am    
Dear @hackersid,
As i wrote in my comments to your question, do NOT post a link to image, please describe your problem. Those are the rules of CP QA board.
In this case, NULL means that Engineer has been not set. You can use COALESCE(EN.[Name],"") to replace NULL's with empty string.
Shajee Afzal 31-Mar-14 2:52am    
Sorry my bad, I didn't knew posting images were against the rules. This will not happen again.
Engineers were assigned to all the tickets. Anyways my problem was solved by adding "PARTITION BY TD.TicketID" in ROW_NUMBER call.
Thank you again.
Schatak 1-Apr-14 2:27am    
Maciej Los : Great work,
@hackersid i was trying the same with CTE. before i submitted you got your solution :)
Maciej Los 1-Apr-14 2:33am    
Thank you, Schatak ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900