通过添加偏差值作为新列来合并行

发布于 2025-02-13 20:30:21 字数 4594 浏览 0 评论 0原文

我想为每个填写特定调查表的人以及来自特定问卷/评估的数据输出。这些值存储在表格标准中。

The result of my current query:

KlientId	Name1	Name2	CriteriaName	Result
335	Name1Person1	Name2Person1	IF1	Yes
335	Name1Person1	Name2Person1	IF2	Yes
335	Name1Person1	Name2Person1	IF3	No
336	Name1Person2	Name2Person2	IF1	Yes
336	Name1Person2	Name2Person2	IF2	Yes
336	Name1Person2	Name2Person2	IF3	No

What 拥有：

klientid	name1	name2	if1 if2	if2	if3 if3
person2name1	person1name1	person1name2	是是否	336	，
person2name2	person2name2	是否	否	所以	我想

标准应该得到自己的列，而引用同一个人的行应该基于同一klientid。我用来获得我当前结果的查询使用了一些加入，以便从人到客户，并在评估到标准的过程中，在该标准中，Criterianame和Result所处的位置。其他表仅与外国键一起使用，以获取这些值“ if1：yes”等。

SELECT Client.Id, Person.Name1, Person.Name2, Person.Birthday, CriteriaDF.CriteriaName, CriteriaDF.Result
FROM Client
INNER JOIN Person ON Client.PersonId=Person.Id
INNER JOIN Process ON Client.Id=Process.ClientId
INNER JOIN AssessmentDF ON Process.Id=AssessmentDF.ProCessId
INNER JOIN CriteriaDF ON AssessmentDF.Id=CriteriaDF.AssessmentDfId
WHERE AssessmentDF.Name='RightAssessmentName' AND AssessmentDF.Date > DATEADD(day, -180, GETDATE())

编辑：使用别名查询：

SELECT t1.Id, t2.Name1, t2.Name2, t2.Birthday, t5.CriteriaName, t5.Result
FROM Client AS t1
INNER JOIN Person AS t2 ON t1.PersonId=t2.Id
INNER JOIN Process AS t3 ON t1.Id=t3.ClientId
INNER JOIN AssessmentDF AS t4 ON t3.Id=t4.ProcessId
INNER JOIN CriteriaDF AS t5 ON t4.Id=t5.AssessmentDfId
WHERE t4.Bezeichnung='RightAssessmentName' AND t4.Date > DATEADD(day, -180, GETDATE())

我的主要问题是如何转换元组criterianame＆amp;结果是每个唯一criterianame的新列，并用结果填充单元格。

我不认为它们很重要，但是这里都是用来从人到标准的最小表）：

餐桌人员：

ID	NAME1	NAME2
2766	PENSIC1NAME1	PERMAN1NAME2
2767	PERMAN2NAME2	person2Name2 NAME2
2768	Person3Name2 person3name2	person3name2

table klient：

id	persyid
1	2766
335	2767
336	2768

：

ID	Klientid
2485	335
2515	336
2519	336

表

ID	ProcessID	日期	名称
43	2485	2022-04-18	RightAssessmentName
44		流程	destmentdf
2515	2022-05-18	评估	名称

表 In reality there is IF1-If19

Id	AssessmentDfId	CriteriaName	ProcessId	Result
551	43	IF1	2485	Yes
552	43	IF2	2485	Yes
553	43	IF3	2485	No
554	44	IF1	2515	Yes
555	44	IF2	2515	Yes
556	44	IF3	2515	No
557	45	IF1	2519	Yes
558	45	IF2	2519	否
559	45	IF3	2519

原文

I want to output for each person that has filled out a specific questionnaire their personal data as well as the data from the specific questionnaire/assessment. The values are stored in the table criteriaDF.

The result of my current query:

KlientId	Name1	Name2	CriteriaName	Result
335	Name1Person1	Name2Person1	IF1	Yes
335	Name1Person1	Name2Person1	IF2	Yes
335	Name1Person1	Name2Person1	IF3	No
336	Name1Person2	Name2Person2	IF1	Yes
336	Name1Person2	Name2Person2	IF2	Yes
336	Name1Person2	Name2Person2	IF3	No

What I want to have:

KlientId	Name1	Name2	IF1	IF2	IF3
335	Person1Name1	Person1Name2	Yes	Yes	No
336	Person2Name1	Person2Name2	Yes	No	No

So the criterias should get their own columns and the rows referencing the same person should merge into one based on the same KlientId.
The query I used to get my current result uses a few joins in order to get from person to client, to process to assessment to criteria, where the CriteriaName and Result lies. The other tables are just used with their foreign keys to get to these values "IF1: Yes" etc.

SELECT Client.Id, Person.Name1, Person.Name2, Person.Birthday, CriteriaDF.CriteriaName, CriteriaDF.Result
FROM Client
INNER JOIN Person ON Client.PersonId=Person.Id
INNER JOIN Process ON Client.Id=Process.ClientId
INNER JOIN AssessmentDF ON Process.Id=AssessmentDF.ProCessId
INNER JOIN CriteriaDF ON AssessmentDF.Id=CriteriaDF.AssessmentDfId
WHERE AssessmentDF.Name='RightAssessmentName' AND AssessmentDF.Date > DATEADD(day, -180, GETDATE())

Edit: Query using aliases:

SELECT t1.Id, t2.Name1, t2.Name2, t2.Birthday, t5.CriteriaName, t5.Result
FROM Client AS t1
INNER JOIN Person AS t2 ON t1.PersonId=t2.Id
INNER JOIN Process AS t3 ON t1.Id=t3.ClientId
INNER JOIN AssessmentDF AS t4 ON t3.Id=t4.ProcessId
INNER JOIN CriteriaDF AS t5 ON t4.Id=t5.AssessmentDfId
WHERE t4.Bezeichnung='RightAssessmentName' AND t4.Date > DATEADD(day, -180, GETDATE())

My main question is how to convert the tuples CriteriaName & Result to a new column for each unique CriteriaName and Fill the cell with the Result.

I dont think they're important, but here are all minimal tables that are used to get from the person to the criterias (Ids might not fit perfectly to the result & what I wanted to have, just to understand how the data is stored):

Table Person: