Convert Row into Columns

• sql server developer

  SSCarpal Tunnel

  Points: 4746

  More actions

  March 20, 2013 at 2:32 pm

  #270064

  Hi All,

  I've a table that has data in the particular format

  ManagerID | EmployeeID

  1001 | 9990

  1001 | 9991

  1002 | 9993

  1002 | 9994

  1003 | 9995

  1003 | 9996

  1003 | 9997

  1003 | 9998

  1003 | 9999

  I would like to get the results as

  ManagerID | EmployeeID1 | EmployeeID2 | EmployeeID3

  1001 | 9990 | 9991 | null

  1002 | 9993 | 9994 | null

  1003 | 9995 | 9996 | 9997

  If you notice correctly ManagerID 1003 has 5 employeeid but i need only 3 of them in ascending order...

  Thanks in advance

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  March 20, 2013 at 4:11 pm

  #1599180

  Here you go. l leave the easy part (eliminate unwanted rows) for you to solve the tough part can be done this way:

  CREATE TABLE #DATA (ManagerID INT, EmployeeID VARCHAR(50));

  ;WITH TEMPTE(ManagerID,EmployeeID) AS(

  SELECT '1001','9990' UNION ALL

  SELECT '1001','9991' UNION ALL

  SELECT '1002','9993' UNION ALL

  SELECT '1002','9994' UNION ALL

  SELECT '1003','9995' UNION ALL

  SELECT '1003','9996' UNION ALL

  SELECT '1003','9997' UNION ALL

  SELECT '1003','9998' UNION ALL

  SELECT '1003','9999')

  INSERT INTO #DATA (ManagerID, EmployeeID)

  SELECT ManagerID, EmployeeID

  FROM TEMPTE;

  CREATE TABLE #TEMP (EmployeeID VARCHAR(50));

  INSERT INTO #TEMP(EmployeeID)

  SELECT DISTINCT EmployeeID AS EmployeeID

  FROM #DATA

  ORDER BY EmployeeID;

  DECLARE @SQL AS VARCHAR(8000)

  SET @SQL = 'SELECT MANAGERID ';

  SELECT @SQL = @SQL + ',[Employee' + EmployeeID+'] =ISNULL((SELECT EmployeeID FROM #DATA WHERE EmployeeID ='+ EmployeeID+ '),NULL)'

  FROM #TEMP

  ORDER BY EmployeeID;

  PRINT @SQL + 'FROM #DATA T1'

  EXEC( @SQL + 'FROM #DATA T1')