Dynamic SQL Server cross tab I would like request how to do cross tab in SQL using two table.

I want achieve result :

        amy           pal          chin 
amy               2017-02-18   2017-02-18
pal                            2017-02-18
chin 2017-02-18                 


Below is my stored procedure:     

USE [IRIS]
GO
/****** Script Date: 02/20/2017 14:03:28 ******/
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[crosstab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[crosstab]
GO

CREATE PROCEDURE CROSSTAB
AS
DECLARE @SQL1 VARCHAR(8000)
DECLARE @VAR1 INT
DECLARE @VAR2 INT
DECLARE @VAR3 VARCHAR(100)
SET @VAR1=(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])
SET @SQL1='SELECT Mysingle, '
WHILE @VAR1 &lt;&gt; 0 
BEGIN
SET @VAR3=(SELECT Mysingle FROM [IRIS].[dbo].[I_User] WHERE Id =@VAR1)
SET @SQL1=@SQL1 +' MAX(CASE Uids WHEN '+CONVERT(VARCHAR(10),@VAR1)+' THEN Dates  ELSE  '''' END) AS ['+CONVERT(VARCHAR(MAX),@VAR3)+'],'
SET @VAR1=@VAR1+1
END
SET @SQL1=LEFT(@SQL1,LEN(@SQL1)-1)
SET @SQL1=@SQL1+' 
 FROM (SELECT 
  A.UserId AS Uids,
  A.DateShcdule AS Dates,
  B.Mysingle AS Mysingle
  FROM [IRIS].[dbo].[NDRoaster] A LEFT JOIN [IRIS].[dbo].[I_User] B 
  ON A.UserId=B.Id)AS MYTABLE GROUP BY Uids'
PRINT @SQL1
EXEC (@SQL1)
Go


I prompt error as below:


  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
  an expression.  SELECT Mysingle   FROM (SELECT    A.UserId AS Uids,
  A.DateShcdule AS Dates,   B.Mysingle AS Mysingle   FROM
  [IRIS].[dbo].[NDRoaster] A LEFT JOIN [IRIS].[dbo].[I_User] B    ON
  A.UserId=B.Id)AS MYTABLE GROUP BY Uids  Msg 8120, Level 16, State
  1,Line 1 Column 'MYTABLE.Mysingle' is invalid in the select list
  because it is not contained in either an aggregate function or the
  GROUP BY clause.


I am following the article SQL Server: Dynamic Cross-Tab

There are 2 tables:

(1) user

[Id],
[Mysingle],
[Password],
[EmpId],
[LevelActive],
[DateCreated],
[LastLoginDate],
[DeptId] ,
[EmailId] ,
[Isactived],
[CommTypeId],
[IPAdd],
[HOD]


(2) Roaster 

[NDR_Id],
[UserId] ,
[DateShcdule],
[DateCreated]


I'm still in learning process on SQL Server.
You are little modify the article example and modification contain error

@VAR1=(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])


will return more than 1 result, and use it as:

@VAR3=(SELECT Mysingle FROM [IRIS].[dbo].[I_User] WHERE Id =@VAR1)


in You case - WHERE Id =@VAR1
not correct

original article use MIN and MAX

@VAR1=(SELECT MAX(UserId) FROM [IRIS].[dbo].[NDRoaster])


You can use any other filter for warrant single result from subquery

Also - wrong loop,
in this case (for each UserID) define cursor as 

(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])


and fetch records in loop


define cursor
tutorial