Geek Answers Handbook

Match duplicate column names and get all values ​​for columns

Explaination

Imagine I have 2 tables. FormFields where the column names are stored as the values ​​to be rotated, and the second table is FilledValues with user-filled values ​​with FormFieldId .

PROBLEM

As you can see (below in the SAMPLE section) in the FormFields table I have duplicate names but different identifiers. I need to do this after joining the tables, all values ​​from the FilledValues table will be FilledValues to column names, not identifiers.

Which is better for me, you will see in the 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

EXIT FOR NOW

 col1 col2 col3 cab -- 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 EXIT

 col1 col2 col3 cab efd

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 create an OUTPUT with multiple lines?

+5
source share
3 answers

Since you use PIVOT, the data is aggregated, so you only return one value for each broken column. You do not have columns in your subquery that are unique and used in the PIVOT grouping aspect to return multiple rows. For this you need some value. If you have a column with a unique value for each "group", you should use it or you can use a window function, such as row_number() .

row_number() will create a serial number for each value of FF.Name , if you have 2 col1 , you will create 1 for the row and 2 for the other 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 a few lines:

 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 . Output:

 | col1 | col2 | col3 | |------|------|------| | c | a | b | | e | f | d |
+2
source

Just adding GroupId to the Pivot source request 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

+2
source

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 not clear which rule is the assignment of a value to a column. This uses a remainder that works for your input.

0
source

More articles:


All Articles