Pivot duplicate column names and get all values for columns

Question

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.

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.

What I need better you will see in OUTPUT section below.

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

SQL FIDDLE

How can I produce the OUTPUT with the multiple rows?

Answer 1

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().

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:

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:

| col1 | col2 | col3 |
|------|------|------|
|    c |    a |    b |
|    e |    f |    d |

Answer 2

Just adding GroupId in Pivot source query will fix your problem

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

SQLFIDDLE DEMO