Dynamic SQL Server cross tab

Question

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 <> 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 =, !=, <, <= , >, >= 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.

Answer 1

I think you're doing too much work.

If this is the desired command:

SELECT u.MySingle
       ,CASE u.Id WHEN 1 THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '' END AS [amy]
       ,CASE u.Id WHEN 2 THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '' END AS [pal]
       ,CASE u.Id WHEN 3 THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '' END AS [chin]
FROM #users u
     LEFT JOIN (SELECT UserId, MAX(DateScheduled) AS MaxScheduled
                FROM #roadster
                GROUP BY UserId) MDates
     ON MDates.UserId = u.Id;

You can do something like this:

DECLARE @SQL1 VARCHAR(MAX);

SELECT @SQL1 = 'SELECT MySingle ' 
                + (SELECT ',' + 'CASE u.Id WHEN ' + CAST(u.Id AS VARCHAR(10))
                              + ' THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '''' END AS ['
                              + u.MySingle + ']'
                   FROM #users u
                   FOR XML PATH(''))
                + ' FROM #users u
                   LEFT JOIN (SELECT UserId, MAX(DateScheduled) AS MaxScheduled
                   FROM #roadster
                   GROUP BY UserId) MDates
                   ON MDates.UserId = u.Id;';

EXEC (@SQL1);

+----------+------------+------------+------------+
| MySingle | amy        |     pal    |    chin    |
+----------+------------+------------+------------+
| amy      | 01/03/2017 |            |            |
+----------+------------+------------+------------+
| pal      |            | 02/03/2017 |            |
+----------+------------+------------+------------+
| chin     |            |            | 03/03/2017 |
+----------+------------+------------+------------+

Check it here: http://rextester.com/CCIXF68831

Answer 2

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

1. define cursor
2. tutorial