EXPLAINATION
Imagine that I have 2 tables. FormFields where are stored column names as values, which should be pivoted and second table FilledValues with user's filled values with FormFieldId provided.
想象一下,我有2张桌子。 FormFields存储列名作为值,应该是旋转的,第二个表FilledValues与用户填充的值一起提供FormFieldId。
PROBLEM
As you see (below in SAMPLE section) in FormFields table I have duplicate names, but different ID's. I need to make that after joining tables, all values from FilledValues table will be assiged to column names, not to Id's.
正如您在FormFields表中看到的(在SAMPLE部分下面)我有重复的名称,但ID不同。我需要在连接表之后,将FilledValues表中的所有值分配给列名,而不是Id。
What I need better you will see in OUTPUT section below.
我需要的更好,您将在下面的OUTPUT部分看到。
SAMPLE DATA
FormFields
ID Name GroupId
1 col1 1
2 col2 1
3 col3 1
4 col1 2
5 col2 2
6 col3 2
FilledValues
ID Name FormFieldId GroupID
1 a 2 1
2 b 3 1
3 c 1 1
4 d 4 2
5 e 6 2
6 f 5 2
OUTPUT FOR NOW
输出现在
col1 col2 col3
c a b -- As you see It returning only values for FormFieldId 1 2 3
-- d, e, f are lost that because It have duplicate col names, but different id's
DESIRED OUTPUT
col1 col2 col3
c a b
e f d
QUERY
SELECT * FROM
(
SELECT FF.Name AS NamePiv,
FV.Name AS Val1
FROM FormFields FF
JOIN FilledValues FV ON FF.Id = FV.FormFieldId
) x
PIVOT
(
MIN(Val1)
FOR NamePiv IN ([col1],[col2],[col3])
) piv
How can I produce the OUTPUT with the multiple rows?
如何生成多行的OUTPUT?
3 个解决方案
#1
2
Since you are using PIVOT the data is being aggregated so you only return one value for each column being grouped. You don't have any columns in your subquery that are unique and being used in the grouping aspect of PIVOT to return multiple rows. In order to do this you need some value. If you have a column with a unique value for each "group" then you would use that or you can use a windowing function like row_number().
由于您正在使用PIVOT,因此正在聚合数据,因此您只为每个要分组的列返回一个值。子查询中没有任何列是唯一的,并且在PIVOT的分组方面用于返回多行。为了做到这一点,你需要一些价值。如果你有一个列为每个“组”具有唯一值,那么你可以使用它,或者你可以使用像row_number()这样的窗口函数。
row_number() will create a sequenced number for each FF.Name meaning if you have 2 col1 you will generate a 1 for a row and a 2 for another row. Once this is included in your subquery, you now have a unique value that is used when aggregating your data and you will return multiple rows:
row_number()将为每个FF创建一个序列号。这意味着如果你有2个col1,你将为一行生成1,为另一行生成2。一旦将其包含在子查询中,您现在就拥有了在聚合数据时使用的唯一值,并且您将返回多行:
SELECT [col1],[col2],[col3]
FROM
(
SELECT
FF.Name AS NamePiv,
FV.Name AS Val1,
rn = row_number() over(partition by ff.Name order by fv.Id)
FROM FormFields FF
JOIN FilledValues FV ON FF.Id = FV.FormFieldId
) x
PIVOT
(
MIN(Val1)
FOR NamePiv IN ([col1],[col2],[col3])
) piv;
See SQL Fiddle with Demo. The output is:
请参阅SQL Fiddle with Demo。输出是:
| col1 | col2 | col3 |
|------|------|------|
| c | a | b |
| e | f | d |
#2
2
Just adding GroupId in Pivot source query will fix your problem
只需在Pivot源查询中添加GroupId即可解决您的问题
SELECT * FROM (
SELECT FF.Name AS NamePiv,
FV.Name AS Val1,
ff.groupid
FROM FormFields FF
JOIN FilledValues FV ON FF.Id = FV.FormFieldId
) x
PIVOT
(
MIN(Val1)
FOR NamePiv IN ([col1],[col2],[col3])
) piv
#3
0
I would be inclined to do this with conditional aggregation:
我倾向于使用条件聚合来做到这一点:
select max(case when formfieldid % 3 = 1 then name end) as col1,
max(case when formfieldid % 3 = 2 then name end) as col2,
max(case when formfieldid % 3 = 0 then name end) as col3
from FilledValues
group by GroupID;
It is unclear what the rule is for assigning a value to a column. This uses the remainder, which works for your input data.
目前还不清楚为列分配值的规则是什么。这使用余数,它适用于您的输入数据。
#1
2
Since you are using PIVOT the data is being aggregated so you only return one value for each column being grouped. You don't have any columns in your subquery that are unique and being used in the grouping aspect of PIVOT to return multiple rows. In order to do this you need some value. If you have a column with a unique value for each "group" then you would use that or you can use a windowing function like row_number().
由于您正在使用PIVOT,因此正在聚合数据,因此您只为每个要分组的列返回一个值。子查询中没有任何列是唯一的,并且在PIVOT的分组方面用于返回多行。为了做到这一点,你需要一些价值。如果你有一个列为每个“组”具有唯一值,那么你可以使用它,或者你可以使用像row_number()这样的窗口函数。
row_number() will create a sequenced number for each FF.Name meaning if you have 2 col1 you will generate a 1 for a row and a 2 for another row. Once this is included in your subquery, you now have a unique value that is used when aggregating your data and you will return multiple rows:
row_number()将为每个FF创建一个序列号。这意味着如果你有2个col1,你将为一行生成1,为另一行生成2。一旦将其包含在子查询中,您现在就拥有了在聚合数据时使用的唯一值,并且您将返回多行:
SELECT [col1],[col2],[col3]
FROM
(
SELECT
FF.Name AS NamePiv,
FV.Name AS Val1,
rn = row_number() over(partition by ff.Name order by fv.Id)
FROM FormFields FF
JOIN FilledValues FV ON FF.Id = FV.FormFieldId
) x
PIVOT
(
MIN(Val1)
FOR NamePiv IN ([col1],[col2],[col3])
) piv;
See SQL Fiddle with Demo. The output is:
请参阅SQL Fiddle with Demo。输出是:
| col1 | col2 | col3 |
|------|------|------|
| c | a | b |
| e | f | d |
#2
2
Just adding GroupId in Pivot source query will fix your problem
只需在Pivot源查询中添加GroupId即可解决您的问题
SELECT * FROM (
SELECT FF.Name AS NamePiv,
FV.Name AS Val1,
ff.groupid
FROM FormFields FF
JOIN FilledValues FV ON FF.Id = FV.FormFieldId
) x
PIVOT
(
MIN(Val1)
FOR NamePiv IN ([col1],[col2],[col3])
) piv
#3
0
I would be inclined to do this with conditional aggregation:
我倾向于使用条件聚合来做到这一点:
select max(case when formfieldid % 3 = 1 then name end) as col1,
max(case when formfieldid % 3 = 2 then name end) as col2,
max(case when formfieldid % 3 = 0 then name end) as col3
from FilledValues
group by GroupID;
It is unclear what the rule is for assigning a value to a column. This uses the remainder, which works for your input data.
目前还不清楚为列分配值的规则是什么。这使用余数,它适用于您的输入数据。