SQL query: different rows into same row

Question

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.

Can you post a sample of your data, current results and expected results? — Sébastien Sevrin, May 13 '15 at 08:17
@SebastianSevrin Sure, But I can't post the exact Tables and Results. — Jane, May 13 '15 at 08:22
As a general point on the query, don't use A, B etc. as aliases. It makes the query completely incomprehensible. How is anyone supposed to know that H.Desc relates to a person's position. — Tobsey, May 13 '15 at 08:44
@Jane, to be honest I don't think I have time now. What you are trying to do is a Pivot. If you know the maximum number of conductors that you will ever have, then you should be able to construct a query with a number of CASE statements and a group by. Otherwise you can write dynamic sql whcih will build your query up depending on the number of conductors. It really depends on what you are trying to achieve. Why can't you select the data relating to the Inspection first, like Description, etc. and then run a second query which gets the inspectors for each. Is this going into a csv/xlsx report? — Tobsey, May 13 '15 at 09:06
@Jane search her for "SQL Pivot" and you will find a number of relevant questions — Tobsey, May 13 '15 at 09:06
@Tobsey is correct that you need some sort of pivoting. But honestly, this is much easier to do in a loop in application code than in SQL. If this is going to be a report of some sort, a master detail layout with a list of inspections would work much better. — KingOfAllTrades, May 13 '15 at 09:16

score 1 · Accepted Answer · answered May 13 '15 at 09:30

1

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)

answered May 13 '15 at 09:30

Sébastien Sevrin

5,267
2
22
39

Thank you for your help. I am going to try `pivot` and get back to you. Yes the conductors are not limited to 3. Is there any way, that I can combine these two queries? Different structure perhaps? – Jane May 13 '15 at 10:25
With an unknown number of conductors, you should go for `XML` or `dynamic SQL`, depending on the goal of the query. Please **do not** write a query with 40 conductors and (yes I saw it once!) – Sébastien Sevrin May 13 '15 at 10:31
@SebastianSevrin could you give me the best link that I can use as reference? Also, I try to use `pivot` in my current query just to see if i can move the second row to the first row, but it encountered error. Could you modify my codes? – Jane May 13 '15 at 10:56
@Jane The pivot link in my answer is a good one to get started, you can look at the `Basic PIVOT Example` section and replace the `AVG` by a `MAX` – Sébastien Sevrin May 13 '15 at 11:01
@SebastianSevrin, i will try again – Jane May 13 '15 at 11:11
@SebastianSevrin,I can't use it – Jane May 13 '15 at 11:28
@SebastianSevrin, could you provide general structure -> `3 times on Employee, EmpPoisition, Position, itemDetails` ? – Jane May 13 '15 at 11:54
`LEFT JOIN Employee E1 ON A.conductor_id = ... LEFT JOIN Employee E2 ON A.conductor_id = ... LEFT JOIN Employee E3 ON A.conductor_id = ...` with `...` your employee selection. This is really bad, I shouldn't even have mentioned it. – Sébastien Sevrin May 13 '15 at 12:13
@SebastianSevrin, I found out what was the problem of my `pivot`query earlier. Since i am using Sql Server 2008, the syntax is a little bit different. However, is there any way that i can use two pivot at the same time? Because you see, i still need to get the totalFiles2 and totalFiles3 at the same row – Jane May 13 '15 at 13:05
I am referring to this solution by @MartinSmith http://stackoverflow.com/questions/8869599/sql-self-join-multiple-times – Jane May 13 '15 at 13:07
[Here](http://pratchev.blogspot.de/2009/01/pivoting-on-multiple-columns.html) you have a sample with multiple `pivot` and [here](http://stackoverflow.com/questions/19590799/tsql-pivot-multiple-columns) a way using `apply` + `pivot` – Sébastien Sevrin May 13 '15 at 13:15
could you look at this http://stackoverflow.com/questions/30218299/pivot-value-is-null @SebastienSevrin ? – Jane May 13 '15 at 14:47

SQL query: different rows into same row

1 Answers1