Sql-server – Dynamic SQL Server cross tab

pivotsql serverstored-procedures

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.

Best Answer

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

Related Question