How to select distinct values and have the differences as seperate columns in the same table

Question

I have a one to many relationship between Course and Facilitator. The foreign key is in the Course table. How do I select a facilitator as distinct and have its multiple course IDs as columns next to the facilitator.

SELECT dbo.Facilitator.Fac_ID, dbo.Facilitator.Fac_Name, dbo.Course.Course_ID
FROM dbo.Course 
RIGHT JOIN dbo.Facilitator ON dbo.Course.FK_Facilitator = dbo.Facilitator.Fac_ID 
order by dbo.Facilitator.Fac_Name asc

returns:

enter image description here

instead, I want:

enter image description here

Take a close look at that question and the accepted answer (_looks like some kind of duplicate to me_): http://stackoverflow.com/a/28696994/4584335 with only a little SQL skills you should be able to get your answer. — Rubik, Mar 05 '15 at 13:30
Agreeing with @Rubik on this one. If this is SQL server, then use PIVOT. If this is damned near any other RDBMS, then you've got some work to do, especially if there can be a large variable number for each name. — JNevill, Mar 05 '15 at 13:39
Forgot to mention, one facilitator can only belong to 1 or 2 courses, never more. — Jnr, Mar 05 '15 at 13:42
possible duplicate of [Transposing rows into columns based on a condition sql](http://stackoverflow.com/questions/28696315/transposing-rows-into-columns-based-on-a-condition-sql) — Tab Alleman, Mar 05 '15 at 14:19

score 0 · Answer 1 · edited Jan 18 '21 at 12:31

For pivoting, the column names should be defined. In your case you you do not have such a column name, as per your question. So we create sample column names like 1,2 etc.

First of all, get the column names for pivot

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + COLUMNNAME + ']', '[' + COLUMNNAME + ']')
               FROM    
               (
                   -- Generates random column names numerically from 1,2 etc
                   SELECT DISTINCT CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 0))AS VARCHAR(4))COLUMNNAME 
                   FROM #TEMP
               ) PV 
               ORDER BY CAST(COLUMNNAME AS INT)

Now pivot the result. I have written the logic inside.

DECLARE @query NVARCHAR(MAX)
SET @query = '-- This outer query forms your pivoted result
             SELECT * FROM 
             (
                -- Source data for pivoting                
                SELECT ID,NAME,VALUE,
                CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 0))AS VARCHAR(4)) COLUMNNAME  
                FROM #TEMP 
             ) x
             PIVOT 
             (
                 --Defines the values in each dynamic columns
                 MIN(VALUE)
                 -- Get the names from the @cols variable to show as column
                 FOR COLUMNNAME IN (' + @cols + ')
            ) p            
            ORDER BY NAME;' 

EXEC SP_EXECUTESQL @query

Click here to view result

Thank you @Sarath Avanavu for the response. It definitely helped point me in the right direction. I still have a lot to learn. — Jnr, Apr 04 '15 at 15:38
So please mark as answer by clicking **TICK** button on the left isde of answer @Jnr — Sarath Subramanian, Apr 05 '15 at 11:10

How to select distinct values and have the differences as seperate columns in the same table

1 Answers1