Convert multiple records into one column

In SQL 2008, I have below table and need to convert into following table creating one record for each

Date    Name    Colors
Nov01   John    Red
Nov02   Mike    Green
Nov02   Mike    Blue
Nov02   Mike    Grey
Nov03   Melissa Yellow
Nov03   Melissa Orange
Nov10   Rita    Pink
Nov10   Rita    Red

Converted

Date    Name    Red Green   Blue    Grey    Yellow  Orange
Nov01   John    Red
Nov02   Mike        Green   Blue    Grey
Nov03   Melissa                 Yellow  Orange
Nov10   Ri开发者_JS百科ta    Red                     Pink

See below pic, as I do not know how to format table here

Many thanks.

---

See:

How to pivot rows into columns (custom pivoting)

---

Looks like you want a Dynamic Pivot.

Add this Stored Procedure:

CREATE PROC [dbo].[pivotsp]
  @query    AS NVARCHAR(MAX),                   -- The query, can also be the name of a table/view.
  @on_rows  AS NVARCHAR(MAX),                   -- The columns that will be regular rows.
  @on_cols  AS NVARCHAR(MAX),                   -- The columns that are to be pivoted.
  @agg_func AS NVARCHAR(257) = N'SUM',          -- Aggregate function.
  @agg_col  AS NVARCHAR(MAX),                   -- Column to aggregate.
  @output   AS NVARCHAR(257) = N'',             -- Table for results
  @debug    AS bit = 0                          -- 1 for debugging
AS

-- Example usage:
--    exec pivotsp
--          'select * from vsaleshistory',
--          'market,marketid,family,familyid,Forecaster,Forecasterid,product,productid',
--          'month',
--          'sum',
--          'ku',
--          '##sales'

-- Input validation
IF @query IS NULL OR @on_rows IS NULL OR @on_cols IS NULL
   OR @agg_func IS NULL OR @agg_col IS NULL
BEGIN
  RAISERROR('Invalid input parameters.', 16, 1);
  RETURN;
END

-- Additional input validation goes here (SQL Injection attempts, etc.)

BEGIN TRY
  DECLARE
    @sql     AS NVARCHAR(MAX),
    @cols    AS NVARCHAR(MAX),
    @newline AS NVARCHAR(2);

  SET @newline = NCHAR(13) + NCHAR(10);

  -- If input is a valid table or view
  -- construct a SELECT statement against it
  IF COALESCE(OBJECT_ID(@query, N'U'),
              OBJECT_ID(@query, N'V')) IS NOT NULL
    SET @query = N'SELECT * FROM ' + @query;

  -- Make the query a derived table
  SET @query = N'(' + @query + N') AS Query';

  -- Handle * input in @agg_col
  IF @agg_col = N'*'
    SET @agg_col = N'1';

  -- Construct column list
  SET @sql =
      N'SET @result = '                                    + @newline +
      N'  STUFF('                                          + @newline +
      N'    (SELECT N'','' +  quotename( '
                   + 'CAST(pivot_col AS sysname)' +
                   + ')  AS [text()]'                          + @newline +
      N'     FROM (SELECT DISTINCT('
                   + @on_cols + N') AS pivot_col'              + @newline +
      N'           FROM' + @query + N') AS DistinctCols'   + @newline +
      N'     ORDER BY pivot_col'                           + @newline +
      N'     FOR XML PATH(''''))'                          + @newline +
      N'    ,1, 1, N'''');'

  IF @debug = 1
     PRINT @sql

  EXEC sp_executesql
    @stmt   = @sql,
    @params = N'@result AS NVARCHAR(MAX) OUTPUT',
    @result = @cols OUTPUT;

  IF @debug = 1
     PRINT @cols

  -- Create the PIVOT query
  IF @output = N''
      begin
        SET @sql =
            N'SELECT *'                                          + @newline +
            N'FROM (SELECT '
                          + @on_rows
                          + N', ' + @on_cols + N' AS pivot_col'
                          + N', ' + @agg_col + N' AS agg_col'        + @newline +
            N'      FROM ' + @query + N')' +
                          + N' AS PivotInput'                        + @newline +
            N'  PIVOT(' + @agg_func + N'(agg_col)'               + @newline +
            N'    FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'
      end
  ELSE
      begin
        set @sql = 'IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE  ' +
            'name = ''' + @output + ''' AND type = N''U'') DROP TABLE tempdb.' + @output
        EXEC sp_executesql @sql;

        SET @sql =
            N'SELECT * INTO ' + @output                          + @newline +
            N'FROM (SELECT '
                          + @on_rows
                          + N', ' + @on_cols + N' AS pivot_col'
                          + N', ' + @agg_col + N' AS agg_col'        + @newline +
            N'      FROM ' + @query + N')' +
                          + N' AS PivotInput'                        + @newline +
            N'  PIVOT(' + @agg_func + N'(agg_col)'               + @newline +
            N'    FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'
      end

    IF @debug = 1
       PRINT @sql

    EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
  DECLARE
    @error_message  AS NVARCHAR(2047),
    @error_severity AS INT,
    @error_state    AS INT;

  SET @error_message  = ERROR_MESSAGE();
  SET @error_severity = ERROR_SEVERITY();
  SET @error_state    = ERROR_STATE();

  RAISERROR(@error_message, @error_severity, @error_state);

  RETURN;
END CATCH

and then

exec pivotsp 'select * from mytable',
  '[date],name',
  'colours',
  'min',
  '#temp'
select * from #temp

(The MIN is a bit of a hack, but it should produce what you want)