CODEWITHSUNDEEP
sqlsql-serverleft-join
Jane
Jane

Reputation: 5

SQL query: different rows into same row

I am a beginner and I am using SQL Server for this particular query. I have to combine data of two rows into one row. The difference is the people who conducted the inspection. I have no problem getting all data. But I cannot put two rows of data into single row. Conductor 1 and Conductor 2 are not supposed to be the same person. Every inspection can be reviewed more than 2 person. So, right now I am trying to group each row by Items. Each item can be reviewed more than 2 person. Right now my conductor2 and onwards are NULL. How can i fix this? The value of conductor2 and onwards went to the next row instead of filling the columns after val1 column.

SELECT Z.* , 
(Z.val1 - z.val2 ) AS Variance FROM(

SELECT 
(SELECT C.desc WHERE C.id = B.inspectionItem_id ) AS Items,
(SELECT C.itemType) AS Type,
(SELECT D.card WHERE D.id = A.CardId) AS CardNo,
(SELECT E.desc WHERE E.id = D.taskId) AS Task,
(SELECT D.No WHERE D.id = A.CardId) AS blockNo,
A.inspectionDate AS Idate, 
(SELECT F.fName WHERE F.id = A.conductor_id) AS conductor1,
(SELECT H.desc WHERE H.id = G.position_id AND A.conductor_id = G.emp_id) AS Position,
(SELECT J.fName) AS Supervisor,
(SELECT (CONVERT(INTEGER,(B.vals1))) + (CONVERT(INTEGER,(B.vals2))) + (CONVERT(INTEGER,(B.vals3))) WHERE B.inspection_id = A.id AND B.inspectionItem_id = C.id AND C.itemType = 'MULTIPLE') AS val1,
(SELECT F.fName WHERE K.inspection_id != B.inspection_id ) conductor2,
(SELECT H.desc WHERE H.id = G.position_id AND A.conductor_id = G.emp_id AND A.id != B.inspection_id) AS Position2,
(SELECT F.fName WHERE F.id = A.emp_id AND A.id != B.inspection_id) AS Staff2,
(SELECT (CONVERT(INTEGER,(B.vals1))) + (CONVERT(INTEGER,(B.vals2))) + (CONVERT(INTEGER,(B.vals3))) WHERE B.inspection_id != A.id AND A.CardId = D.id AND A.conductor_id != F.id AND C.itemType = 'MULTIPLE') AS val2


FROM Inspection A

LEFT JOIN Details B ON B.inspection_id = A.id
LEFT JOIN inspectionItem C ON C.id = B.inspectionItem_id
LEFT JOIN Card D ON D.id = A.CardId
LEFT JOIN Master E ON E.id = D.taskId
LEFT JOIN Employee F ON F.id = A.conductor_id
LEFT JOIN Employee J ON J.id = A.emp_id
LEFT JOIN EmployeePosition G ON G.emp_id = F.id
LEFT JOIN Position H ON H.id = G.Position_id
LEFT JOIN Details K ON K.inspection_id = A.id

)Z

WHERE Z.itemType = 'MULTIPLE'

I cannot post the exact Tables nor its sample. So I prepared few tables, and the expected results.

Table Employee

   Id | fName | lName 
   ---| ----- | ----- 
    1 |Michael| John
    2 | Angie | Kors
    3 | Jesse | Cottrell
    4 | James | McFadden
    5 | Flynn | Gabriel
    6 | Cath  | Tan

Table EmpPosition

Id | positionId | empId
---| ---------- | ---
1  |    2       | 1
2  |    3       | 2
3  |    4       | 3
4  |    5       | 4
5  |    2       | 5
6  |    3       | 6

Table Position

   Id | desc 
  --- | --- 
    1 | Admin
    2 | Intern
    3 | Assistant Manager
    4 | Manager
    5 | General Manager

Table mainInspection

   Id | cardNo | conductedById | supervisedById | inspectionDate
  --- | ------ | ------------- | -------------- | -------------------------
    1 | 001    |      1        |       2        | 2015-03-11 10:40:00.000
    2 | 001    |      3        |       4        | 2015-03-11 11:40:18.000
    3 | 001    |      6        |       5        | 2015-03-11 12:00:31.317
    4 | 002    |      1        |       2        | 2015-03-11 13:50:10.000
    5 | 002    |      3        |       4        | 2015-03-11 14:20:51.424
    6 | 002    |      6        |       5        | 2015-03-11 15:26:15.507

Table regCard

   Id | cardNo | colorCardId | taskId | roomNo
  --- | ------ | ----------- | --- | ---
    1 |   001  |      1      | 1   | 10
    2 |   002  |      1      | 2   | 8

Table itemDescription

   Id | itemDescription
    1 | Purchase Record Files
    2 | Sales Record Files

Table Task

Id| taskDesc
1 | Annual Report
2 | Monthly Report

Table itemDetails

Id | mainInspectionId | itemDescriptionId | completeQuantity | notCompleteQuantity | totalFiles
1  |         1        |         1         |        10        |         15          |     25
2  |         2        |         1         |         8        |          4          |     12
3  |         3        |         1         |         1        |          1          |     2
4  |         4        |         2         |        16        |          5          |     21
5  |         5        |         2         |         8        |          0          |     8
6  |         6        |         2         |         8        |          2          |     10

Table Color

   Id | colorCard | desc
  ----| ----------| ---------------
    1 |    Red    | lastYearRecord
    2 |   Yellow  | latestRecord

My query result should be like this:

    itemDescription      | cardNo | colorCard |   taskDesc     | roomNo | inspectionDate | conductor 1  | Position | totalFiles1 |   conductor 2  | Position | totalFiles2 |  conductor 3  | Position | totalFiles3 | Variance (totalFiles1 -totalFiles2 – totalFiles3)


   --------------------- | ------ | ----------| -------------- | ------ | -------------- | -------------| -------- | ----------- | -------------- | ---------| ----------- | ------------- | -------- | ----------- | ------------------------------------------------ 
   Purchase Record Files |   001  |    Red    | lastYearRecord |   10   |   2015-03-11   | Michael John |  Intern  |      25     | Jesse Cottrell | Manager  |     12      | Flynn Gabriel |  Intern  |      2      |                       11
   Sales Record Files    |   002  |  Yellow   | latestRecord   |    8   |   2015-03-11   | Micheal John |  Intern  |      21     | Jesse Cottrell | Manager  |      8      | Flynn Gabriel |  Intern  |      10     |                        3

In my case, I still cannot fetch result for Conductor 2 and onwards.

Upvotes: 0

Views: 2542

Answers (1)

Sébastien Sevrin
Sébastien Sevrin

Reputation: 5405

I looked at your tables, table names and columns names doesn't match original query.

Anyway, I can't provide an copy/paste & run query, but I will try puting you on the right track.

First step would be to make the query easier to read:

  • You don't have to create subqueries for each column to select.
  • You should use relevant aliases names, even if you want to use a single character alias (ex: T for Task, P for Position, ...)

To resolve your issue:

  • You could use a PIVOT or an APPLY operator to show conductor 1, 2, 3 on the same row.

  • Another way could be to join 3 times on Employee, EmpPoisition, Position, itemDetails but it is would be hard to maintain, so you should not use this solution.

Out of scope advices:

  • You should use a unique naming convention/case and keep it for the whole database schema.

  • If conductors are not limited to 3 (N conductors for each cards), this is not a good idea to design such a query, you should show the different conductors on different rows or use an XML datatype. We don't know what this query will be used for but you might be able to split it into 2 queries:

    • A query to show the item details (itemDescription, cardNo, colorCard, taskDesc, roomNo, inspectionDate)
    • A query for the conductors (cardNo, conductor, Position, totalFiles)

Upvotes: 1

Related Questions