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.

• vonPryz: Please edit the question and add table descriptions and source data too.
• user3538475: @vonPryz There are 2 table: 1) user = [Id],[Mysingle],[Password],[EmpId],[LevelActive],[DateCreated] ,[LastLoginDate],[DeptId] ,[EmailId] ,[Isactived],[CommTypeId] ,[IPAdd],[HOD] 2) Roaster = [NDR_Id],[UserId] ,[DateShcdule],[DateCreated] There moret than 1k data inside
• vonPryz: Please edit the question, not add a comment. Comments with code are hard to read and the question itself becomes hard to parse in meaningful a way if pieces of data are here and there.
• a_vlad: I little edit tables in Your post, if You add tables structure - good to add types as well
• user3538475: @vonPryz noted & updated. the data more than 1k :(

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

• user3538475: Sorry , i just realise made mistake on my question on my expteced result. i made change on result. i want similar like footbal fixture..cannot fall date on same name..

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

• user3538475: I able get data using CURSOR but it fetch all data into diffrent table. i want all data into single table
• a_vlad: what are You mean? not sure - it good idea to discus in comments, but cursor always fetch as defined, so - something wrong. it proper replacement for You WHILE VAR1 - fetch into VAR1 ...
• user3538475: when i put this condition EXEC (@SQL1) inside while, it excute 5 diffrent table result. (not in single table). if outside loop, it excute 1 table only. (last user only). it not achive expected result i want.
• user3538475: do you want me update new code ?