0

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.

  • Please edit the question and add table descriptions and source data too. – vonPryz Feb 20 '17 at 9:15
  • @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 – user3538475 Feb 20 '17 at 9:28
  • 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. – vonPryz Feb 20 '17 at 9:32
  • I little edit tables in Your post, if You add tables structure - good to add types as well – a_vlad Feb 20 '17 at 9:43
  • @vonPryz noted & updated. the data more than 1k :( – user3538475 Feb 20 '17 at 9:43
0

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

  • 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.. – user3538475 Feb 21 '17 at 2:30
0

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
  • I able get data using CURSOR but it fetch all data into diffrent table. i want all data into single table – user3538475 Feb 20 '17 at 10:29
  • 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 ... – a_vlad Feb 20 '17 at 10:31
  • 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 Feb 20 '17 at 10:44
  • do you want me update new code ? – user3538475 Feb 20 '17 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.