How to Pivot a Sql query In Ignition

Ignition
1

What I have below gives me the results From the EquipWorkOrder Table and EquipWorkOrderHrs Table Linked by EquipWorkOrderID. The Other joins are for info on equipment. I can Have up to 5 Techs or UserNm’s and each one of them has Hrs associated with it. I don’t want to duplicate the row if there is more than one UserNm per the EquipWorkOrder ID.

Example of my current query. I left some of them off for Example

EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm/Hrs/and so on

1 / 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ John / 1/ ...

1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Mike / 1/ ..

1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Billy / 1/...

1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Aaron/ 1/ ...

1/ 08/26/2020 08:46:16 /Gator/01235/John Deere Loader/ Test/Test/ Completed/ Jason/ 2/ ...

2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mark/ 2/ ...

2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Trent/ 2/ ...

3/ 08/26/2020 08:52:00 /Welder/01236/Miller Welder/ Test/Test/ Completed/ John/ 2/ ...

4 08/26/2020 08:53:00 /Skidsteer/01236/John Deere Skidsteer/ Test/Test/ Completed/ Mike/ 3/ ...

4 08/26/2020 08:53:00 /Skidsteer/01236/John Deere Skidsteer/ Test/Test/ Completed/ Tony/3/ ...

5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Tim  2/ ...

5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Joe  2/ ...

5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Jeff  2/ ...

5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mike  2/ ...

What I would Like to see.

EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm1/Hrs1/UserNm2/Hrs2/UserNm3/Hrs3/UserNm4/Hrs4/UserNm5/Hrs5/and so on

1 / 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ John / 1/Mike/1/Billy/1/Aaron/1/Jason/1/

2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mark/ 2/ Trent/2/

3/ 08/26/2020 08:52:00 /Welder/01236/Miller Welder/ Test/Test/ Completed/ John/ 2/

4 08/26/2020 08:53:00 /Skidsteer/01235/John Deere Skidsteer/ Test/Test/ Completed/ Mike/ 3/ Tony/ 3/...

5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Tim  / 5/ Joe/5/Jeff/5/Mike/5/...

SELECT Top 1000 a.EquipWorkOrderID, a.DateTm, f.Equip, f.AccountCode, f.Descr,a.Description, a.Resolution, g.Status, c.UserNm, b.Hrs, d.Category, i.MaintLoc, j.EquipType, e.Crew, a.MeterReading,k.Type, a.Comments
FROM EquipWorkOrder a
	Left Join EquipWorkOrderHrs b 
		On a.EquipWorkOrderID = b.EquipWorkOrderID
	Left Join AppUser c
		On c.UserID = b.UserID
	Left Join Category d 
		On a.CategoryID = d.CategoryID 
	Left Join Crew e 
		On e.CrewID = a.CrewID
	Left Join Equipment f 
		On a.EquipmentID = f.EquipmentID
	Left Join Status g 
		On a.StatusID = g.StatusID
	Left Join PlantLoc h 
		On a.PlantLocID = h.PlantLocID 
	Left Join MaintLocation i 
		On a.MaintLocationID = i.MaintLocationID
	Left Join EquipType j 
		On j.EquipTypeID = a.EquipTypeID
	Left Join Type k 
		On f.TypeID = k.TypeID
order by a.DateTm Desc
2

Check out this article on how to do pivot in SQL server.
https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/#:~:text=You%20follow%20these%20steps%20to%20make%20a%20query,expression%20(CTE)%203%20Third%2C%20apply%20the%20PIVOT%20operator.

3

I am looking over the article and trying to come up with a query that works.

4

I am assuming this is the layout I need. But based on the example I don’t know what to add next to get the results I need.

Select * From
(
	Select
		a.EquipWorkOrderID, a.DateTm, f.Equip, f.AccountCode, f.Descr,a.Description, a.Resolution, g.Status, c.UserNm, b.Hrs, d.Category, i.MaintLoc, j.EquipType, e.Crew, a.MeterReading,k.Type, a.Comments
	From
		EquipWorkOrder a
	
	Left Join EquipWorkOrderHrs b
		On a.EquipWorkOrderID = b.EquipWorkOrderID
	Left Join AppUser c
		On c.UserID = b.UserID
	Left Join Category d
		On a.CategoryID = d.CategoryID
	Left Join Crew e
		On e.CrewID = a.CrewID
	Left Join Equipment f
		On a.EquipmentID = f.EquipmentID
	Left Join Status g
		On a.StatusID = g.StatusID
	Left Join PlantLoc h
		On a.PlantLocID = h.PlantLocID
	Left Join MaintLocation i
		On a.MaintLocationID = i.MaintLocationID
	Left Join EquipType j
		On j.EquipTypeID = a.EquipTypeID
	Left Join Type k
		On f.TypeID = k.TypeID
) t
PIVOT(


) AS pivot_table
5

Consider testing just on the table needed for the pivot until you get that part working, then add the rest of the joins you need.

6 
	Select
		a.EquipWorkOrderID,c.UserNm, b.Hrs
	From
		EquipWorkOrder a
	
	Left Join EquipWorkOrderHrs b
		On a.EquipWorkOrderID = b.EquipWorkOrderID
	Left Join AppUser c
		On c.UserID = b.UserID
7

You can’t pivot two columns together. You pivot one column grouped by a second column, where that second column supplies the column names for the split-out parts of the first column.

1 Like
8

You lost me at “You can’t…” can you simplify what you mean

9

In a pivot clause like ... PIVOT xx ON yy ... the column xx provides the content while yy provides the column names. You wont get a column for names and a column for content.

10

Here is what I came up with. This pivots the table with out error but it doesn’t give me the results I need. It puts 0’s in all the Tech columns values, and it also doesn’t show me the User Names (UserNm).

Select * From

(
	Select	
		a.EquipWorkOrderID,
		c.UserNm, 
		b.Hrs
	From
		EquipWorkOrder a
	
	Left Join EquipWorkOrderHrs b
		On a.EquipWorkOrderID = b.EquipWorkOrderID
	Left Join AppUser c
		On c.UserID = b.UserID
) t
Pivot (
		Count(Hrs)
		For UserNm  IN (
			[Tech1],
			[Tech2],
			[Tech3],
			[Tech4],
			[Tech5])
) AS pivot_table
11

Cut this out of your query and see what column names you get. Expect case and possible extra spaces to matter.

I would have expected you to use sum(Hrs) here, but whatever.

It can't show you the user names as a column because it is using them to make column names. For any given row, there isn't one user name to put in that row.

12

Here is an example to help demonstrate what @pturmel pointed out.

I created some temporary tables to work with, that looks like the following:

--If the temp tables already exist then drop them prior to creating them
--This allows the query to be run repeatedly without error due to the
--tables already existing in the TempDB
IF OBJECT_ID('tempdb..#TempEquipWorkOrderHrs') IS NOT NULL
	DROP TABLE #TempEquipWorkOrderHrs

IF OBJECT_ID('tempdb..#TempAppUser') IS NOT NULL
	DROP TABLE #TempAppUser

CREATE TABLE #TempEquipWorkOrderHrs(
	EquipWorkOrderID INT IDENTITY PRIMARY KEY,
	Hrs DEC(10,2) NOT NULL,
	UserID INTEGER NOT NULL
	);

CREATE TABLE #TempAppUser(
	UserID INT IDENTITY PRIMARY KEY,
	UserNm VARCHAR(20)
	);

INSERT INTO #TempEquipWorkOrderHrs
VALUES (4,1),(6,1),(8,2),(4,2),(6,3),(6,3),(2,3),(8,4),(8,4),(10,5),(8,5)

INSERT INTO #TempAppUser
VALUES ('User1'),('User2'),('User3'),('User4'),('User5')

This is just simple data that allows for visualizing what is actually happening, you will probably want to use real data.

The first query shows the results of just the Standard Data prior to the Pivot Operation.

Select UserNm, Hrs
FROM #TempEquipWorkOrderHrs a
INNER JOIN #TempAppUser b on a.UserID = b.UserID

This results in a table that is 2 Columns (UserNm, Hrs) with 11 rows.

The second query shows the results of the Infered Group By statement that the Pivot preforms. Pivot will perform the aggregation For Each UserNm IN the set of columns (User1,User2,User3,User4,User5). So the first step is to run the Aggregation against the source data, grouped by UserNm

Select UserNm, SUM(Hrs) AS 'Total Hours'
FROM #TempEquipWorkOrderHrs a
INNER JOIN #TempAppUser b on a.UserId = b.UserID
GROUP BY UserNm

This results in a table 2 columns (UserNm, Total Hours) by 5 Rows (1 for each UserNm) with the Total Hours being the Sum of all Hours for that User

The Final query is the Actual Pivot query, it takes the results of the second query and rotates the table.

SELECT 'Total Hours' AS Hours_By_Tech,
User1,User2,User3,User4,User5
FROM
(SELECT UserNm,Hrs 
    FROM #TempEquipWorkOrderHrs a
	INNER JOIN #TempAppUser b ON a.UserID = b.UserID) AS SourceTable
PIVOT
(
SUM(Hrs)
FOR UserNm IN (User1,User2,User3,User4,User5)
) AS PivotTable;

This results in a table that is 5 Columns (Hours_By_Tech,User1,User2,User3,User4,User5) and 1 Row showing the Sum(Hrs) for each user.

I am not sure that a PIVOT operation is what you are really after here. Though I am not exactly sure what information you are trying to display either.

13

This is to try to clear up any confusion. Count or sum is not the functions I need. I just want to display the data. That is the only way I could figure it out to make the pivot work. I think I understand now what your talking about. Well… may be I am going about this the wrong way. I need to populate the UserNm with there Hrs. The attached pic show what the current results are before doing the pivot. This has all the tables joined together. If the EquipWorkOrderID is the same then I want to put each UserNm and there Hrs in the same row. So I can do a Sql update statement in the long run

edit work order
edit work order1774×865 45.5 KB
In the Table the Green boxes show what gets populated into the component fields below.based on the selected row. The original insert had 5 tech thats why the ID is the same if you notice it is 7. My name is in all 5 spots for testing purposes. Because on the joining of EquipWorkOrder and EquipWorkOrderHrs tables it duplicates the entry’s for the other columns but will show different UserNm’s if there in there.

14

I don't know how to make this stuff work if they're the same. Make up different names to test with. And yes, PIVOT is probably not the right tool. FWIW, you probably need to separate the query that provides the WO->Tech->Hours relationship from the rest of the data you are displaying. If the number of techs per work order can vary (presumably), you probably should have that as an editable table instead of a fixed set to data entry components.

15

I came across one that uses MAX(CASE…) Can’t tell if it would be a good use case for what you’re doing.