Pivot Rows To Columns With More Than 1 Value Returned

I am currently working on a system that has 2 tables set up like so: Table_1 ------- ID Table2ID Value Table_2 -------- ID ColumnName Some mock results from each table: Table_1

Solution 1:

PIVOT requires the use of an aggregate function to get the result, in your case you are using the min function which, in your query, will return only one value for First_Set and Second_Set. I would suggest including a column that will be used to keep the rows distinct when applying the PIVOT.

For your data, I would suggest using row_number() to generate a unique value for each item in the sets. This value will then be used in the grouping aspect of the PIVOT:

SELECT [First_Set], [Second_Set]
FROM
(
  SELECT B.ColumnName, A.Value
    , row_number() over(partition by a.Table2ID
                        orderby a.Value) seq
  FROM Table_1 AS A 
  INNER JOIN Table_2 AS B 
    ON A.Table2ID = B.ID
) AS P
PIVOT
(
  min(P.Value)
  for P.ColumnName in ([First_Set], [Second_Set])
) AS PIV;

See SQL Fiddle with Demo. This will give a result:

| FIRST_SET | SECOND_SET |
|      ABCD |       MNOP |
|      EFGH |       QRST |
|      IJKL |       UVWX |