About this 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.
