T-SQL How-to Question
-
I need to create a report where each row shows information that I will get from four different records. How can I write a select statement that will combine the four rows from the table into one row for the report?Here is the select statement that would pull back four rows (one row's worth in a report). The workorders table holds 4 records per site number.Select Distinct site_name, state, rollout, WorkOrders.WorkOrderID, WorkOrders.WorkOrderNumber, WorkOrders.Price1, WorkOrders.Price2, WorkOrders.PriceFinalFrom WorkOrders Join sites On WorkOrders.Site_no = @siteNoOrder By WorkOrders.WorkOrderID
-
Join table to itself and filter data for each joined part of the table.
Select WO1.SiteId, ...
FROM WorkOrders WO1
INNER JOIN WorkOrders WO2 on WO1.SiteId = WO2.SiteId
INNER JOIN WorkOrders WO3 on WO1.SiteId = WO3.SiteId
INNER JOIN WorkOrders WO4 on WO1.SiteId = WO4.SiteId
WHERE WO1.WorkOrderId = ...
AND WO2.WorkOrderId = ...
AND WO3.WorkOrderId = ...
AND WO4.WorkOrderId = ...
GROUP BY WO1.SiteId, ...
And please don't make post this way anymore. It was not the easiest post to read.
_____________
Code for TallyGenerator -
Thanks. That worked great. Sorry about the post. I pasted in the question and I did not know that it would not wrap.
-
I spoke too soon. It is not giving me the exact results I want. Can you look at it and see if I have the syntax correct that you intended? Thank you.
Select S.site_name, S.State, S.rollout, WO1.WorkOrderID, WO1.WorkOrderNumber, O1.Price1, WO1.Price2, WO1.PriceFinal, WO2.WorkOrderNumber, WO2.Price1, WO2.Price2, O2.PriceFinal, WO3.WorkOrderNumber, WO3.Price1, WO3.Price2, WO3.PriceFinal, WO4.WorkOrderNumber, WO4.Price1, WO4.Price2, WO4.PriceFinal
FROM WorkOrders WO1
Join Sites S On S.site_no = WO1.site_no
Join WorkOrders WO2 on WO1.site_no = WO2.site_no
Join WorkOrders WO3 on WO1.site_no = WO3.site_no
Join WorkOrders WO4 on WO1.site_no = WO4.site_no
Where WO1.WorkOrderId = WO1.WorkOrderID
AND WO2.WorkOrderId = WO1.WorkOrderID
AND WO3.WorkOrderId = WO1.WorkOrderID
AND WO4.WorkOrderId = WO1.WorkOrderID
And S.rollout = 'WI Transitional Rollout 2005'
-
Is it giving you too many rows?
try specifying what the value of WO1 is?
Without knowing your table relationships, and data its hard to say.
-
You might try one of the following techniques. The first two require you to know the values for workordernumbers, the third does not:
DECLARE @WorkOrderNumberOne int, @WorkOrderNumberTwo int, @WorkOrderNumberThree int, @WorkOrderNumberFour int
SELECT @WorkOrderNumberOne = 1, @WorkOrderNumberTwo = 2, @WorkOrderNumberThree = 3, @WorkOrderNumberFour = 4
-- Solution #1
SELECT S.Site_Name, S.State, S.Rollout, WO.WorkOrderID,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberOne THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_1,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberOne THEN Price1 ELSE NULL END) AS Price1_1,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberOne THEN Price2 ELSE NULL END) AS Price2_1,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberOne THEN PriceFinal ELSE NULL END) AS PriceFinal_1,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberTwo THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_2,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberTwo THEN Price1 ELSE NULL END) AS Price1_2,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberTwo THEN Price2 ELSE NULL END) AS Price2_2,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberTwo THEN PriceFinal ELSE NULL END) AS PriceFinal_2,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberThree THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_3,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberThree THEN Price1 ELSE NULL END) AS Price1_3,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberThree THEN Price2 ELSE NULL END) AS Price2_3,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberThree THEN PriceFinal ELSE NULL END) AS PriceFinal_3,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberFour THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_4,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberFour THEN Price1 ELSE NULL END) AS Price1_4,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberFour THEN Price2 ELSE NULL END) AS Price2_4,
MAX(CASE WHEN WO.WorkOrderNumber= @WorkOrderNumberFour THEN PriceFinal ELSE NULL END) AS PriceFinal_4
FROM WorkOrders WO INNER JOIN
Site S
ON S.Site_No = WO.Site_No
AND S.Rollout = 'WI Transitional Rollout 2005'
group by S.Site_Name, S.State, S.Rollout, WO.WorkOrderID
-- Solution #2
Select S.site_name, S.State, S.rollout,
WO1.WorkOrderID,
WO1.WorkOrderNumber, WO1.Price1, WO1.Price2, WO1.PriceFinal,
WO2.WorkOrderNumber, WO2.Price1, WO2.Price2, WO2.PriceFinal,
WO3.WorkOrderNumber, WO3.Price1, WO3.Price2, WO3.PriceFinal,
WO4.WorkOrderNumber, WO4.Price1, WO4.Price2, WO4.PriceFinal
FROM WorkOrders WO1 Join
Site S On S.site_no = WO1.site_no Join
WorkOrders WO2 on WO1.site_no = WO2.site_no
Join WorkOrders WO3 on WO1.site_no = WO3.site_no
Join WorkOrders WO4 on WO1.site_no = WO4.site_no
Where WO1.WorkOrderNumber = @WorkOrderNumberOne
AND WO2.WorkOrderNumber = @WorkOrderNumberTwo
AND WO3.WorkOrderNumber = @WorkOrderNumberThree
AND WO4.WorkOrderNumber = @WorkOrderNumberFour
And S.rollout = 'WI Transitional Rollout 2005'
-- Solution #3
SELECT S.Site_Name, S.State, S.Rollout, WO.WorkOrderID,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberOne THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_1,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberOne THEN Price1 ELSE NULL END) AS Price1_1,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberOne THEN Price2 ELSE NULL END) AS Price2_1,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberOne THEN PriceFinal ELSE NULL END) AS PriceFinal_1,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberTwo THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_2,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberTwo THEN Price1 ELSE NULL END) AS Price1_2,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberTwo THEN Price2 ELSE NULL END) AS Price2_2,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberTwo THEN PriceFinal ELSE NULL END) AS PriceFinal_2,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberThree THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_3,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberThree THEN Price1 ELSE NULL END) AS Price1_3,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberThree THEN Price2 ELSE NULL END) AS Price2_3,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberThree THEN PriceFinal ELSE NULL END) AS PriceFinal_3,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberFour THEN WorkOrderNumber ELSE NULL END) AS WorkOrderNumber_4,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberFour THEN Price1 ELSE NULL END) AS Price1_4,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberFour THEN Price2 ELSE NULL END) AS Price2_4,
MAX(CASE WHEN WO.WorkOrderNumber= WON.WorkOrderNumberFour THEN PriceFinal ELSE NULL END) AS PriceFinal_4
FROM WorkOrders WO INNER JOIN
Sites S
ON S.Site_No = WO.Site_No
AND S.Rollout = 'WI Transitional Rollout 2005' INNER JOIN
(SELECT WO.Site_No, WO4.WorkOrderMin WorkOrderNumberOne, MIN(WO.WorkOrderNumber) WorkOrderNumberTwo,
MAX(WO.WorkOrderNumber) WorkOrderNumberThree, WO4.WorkOrderMax WorkOrderNumberFour
FROM WorkOrders WO INNER JOIN
(SELECT WO1.Site_No, MIN(WO1.WorkOrderNumber) WorkOrderMin, MAX(WO1.WorkOrderNumber) WorkOrderMax
FROM WorkOrders WO1
GROUP BY WO1.Site_No) WO4
ON WO.Site_No = WO4.Site_No
WHERE WO.WorkOrderNumber NOT IN (SELECT MIN(WO2.WorkOrderNumber)
FROM WorkOrders WO2
WHERE WO2.Site_No = WO.Site_No
UNION
SELECT MAX(WO3.WorkOrderNumber)
FROM WorkOrders WO3
WHERE WO3.Site_No = WO.Site_No)
GROUP BY WO.Site_No, WO4.WorkOrderMin, WO4.WorkOrderMax) WON
ON S.Site_No = WON.Site_No
GROUP BY S.Site_Name, S.State, S.Rollout, WO.WorkOrderID
-
Actually, I figured out what I was doing wrong. Ray, thanks for your response of specifying the value of WO1. That clued me into what I was doing wrong. I was originally getting too many rows. Also...thanks for the etiquette link I will read it over and try to make my posts clearer.
-
I guess I also have to remember to hit enter to make my sentences wrap
-
I probbly did not understand you dat right, but there must be something what can distinguish one WorkOrder from another.
I assumed it is WorkOrderId, but you set them equal in all "subsets" of the table.
So, what's different in WO1, WO2, WO3 and WO4? Include it into WHERE clause and you'll get right result.
For example,
WHERE ...
AND WO1.Task = 'PLANNING'
AND WO2.Task = 'DESIGN'
AND WO3.Task = 'Contracting 3rd party'
AND WO4.Task = 'After Job Drinking'
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply