sql - PIVOT rows to columns with more than 1 value returned

Question

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

Table2ID   |   Value
---------------
1          |   ABCD
1          |   EFGH
1          |   IJKL
2          |   MNOP
2          |   QRST
2          |   UVWX


Table_2

ID   |   ColumnName
--------------------
1    |   First_Set
2    |   Second_Set

So, I have the following query, attempting to turn Table_2's row results into columns

SELECT *
FROM(
SELECT B.ColumnName, A.Value
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

The problem is that, as it's written, I get back a single result. My returned value would be something like this:

    First_Set  |  Second_Set
    -------------------------
    ABCD       |  MNOP

What I want is ALL of the result for each column, but I haven't been able to find a method of using PIVOT that lets me do that.

Does anyone have a suggestion on the best way to turn rows into columns and then receive multiple results for each column?

Answer 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
                        order by 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 |