-2

Projects:

GUID   Name
1    P1
2    P2
3    P3

Projecttasks:

Name   ProjectID Time
PCO1   1         3600
PCA1   1         7200
PCO2   2         1000
PCA2   2         1500
PCO3   3         2000
PCA3   3         1250

Result columns: Projects.Name, Projecttasks.Name (condition 1 = %PCA%) ,Projecttasks.Time (CON1), Projecttasks.Name (condition 2 = %PCO%), Projecttasks.Time (CON2)

P1 PCA1 7200 PCO1 3600
P2 PCA2 1500 PCO2 1000
P3 PCA3 1250 PCO3 2000

I tried

SELECT 
    projects.name,
    (SELECT Projecttasks.Name FROM ProjectTasks WHERE ProjectTasks.Name like '60 MATERIAL PCO%') AS PCO,
    (SELECT Projecttasks.Name FROM ProjectTasks WHERE ProjectTasks.Name like '60 MATERIAL PCA%') AS PCA
FROM
    projects
    Inner Join Projecttasks on Projects.GUID = ProjectTasks.ProjectID

but with error

Msg 512, Level 16, State 1, Line 10 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Pale
  • 1
  • SELECT Projects.Name, (case when ProjectTasks.Name like '%60 MATERIAL PCA%' then Projecttasks.Name end) AS PCA, (case when ProjectTasks.Name like '%60 MATERIAL PCO%' then Projecttasks.Name end) AS PCO, (case when ProjectTasks.Name like '%60 MATERIAL PEI%' then Projecttasks.Name end) AS PEI FROM Projects INNER JOIN Projecttasks ON Projects.GUID = Projecttasks.ProjectID where Projects.name like 'M%' and ProjectTasks.Name like '%60 MATERIAL %' Order BY Projects.Name – Pale Feb 25 '23 at 19:07
  • but it e.g. showes me for each condition new row ... So I have for each Projects 3 rows and goal is to have in one row all information – Pale Feb 25 '23 at 19:09
  • Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad definition/query/function with the obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & ideally a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] – philipxy Feb 25 '23 at 21:13
  • Please clarify via edits, not comments. But don't edit in a way that invalidates reasonable existing answer posts. PS When pinned down by a [mre] this will be a faq. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Feb 25 '23 at 21:13

1 Answers1

0

A simple pivot should help you, as long as you have only 1 PCO/PCA per GUID, if you have more, you need to check the agggregation function, to see if you get the correct name and time

SELECT
    p.Name,
    MAX(CASE WHEN pt.Name like '60 MATERIAL PCO%' THEN pt.Name END PCO_name,
    MAX(CASE WHEN pt.Name like '60 MATERIAL PCO%' THEN pt.Time END PCO_Time,
    MAX(CASE WHEN pt.Name like '60 MATERIAL PCA%' THEN pt.Name END PCA_name,
    MAX(CASE WHEN pt.Name like '60 MATERIAL PCA%' THEN pt.Time END PCA_Time
FROM Projects p INNER JOIN Projecttasks pt ON p.GUID    = pt.ProjectID 
GROUP BY p.GUID,p.Name
nbk
  • 45,398
  • 8
  • 30
  • 47