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.

Please edit the question and add table descriptions and source data too. — vonPryz
– vonPryz, Commented Feb 20, 2017 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
– user3538475, Commented Feb 20, 2017 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
– vonPryz, Commented Feb 20, 2017 at 9:32
I little edit tables in Your post, if You add tables structure - good to add types as well — a_vlad
– a_vlad, Commented Feb 20, 2017 at 9:43

McNets · Accepted Answer · 2017-02-20 12:25:01Z

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
– user3538475, Commented Feb 21, 2017 at 2:30

a_vlad · Accepted Answer · 2017-02-20 09:24:53Z

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

edited Feb 20, 2017 at 9:24

answered Feb 20, 2017 at 9:19

a_vlad

3,7152 gold badges14 silver badges17 bronze badges

I able get data using CURSOR but it fetch all data into diffrent table. i want all data into single table

user3538475
– user3538475

2017-02-20 10:29:31 +00:00
Commented Feb 20, 2017 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
– a_vlad

2017-02-20 10:31:30 +00:00
Commented Feb 20, 2017 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
– user3538475

2017-02-20 10:44:08 +00:00
Commented Feb 20, 2017 at 10:44
do you want me update new code ?

user3538475
– user3538475

2017-02-20 10:44:42 +00:00
Commented Feb 20, 2017 at 10:44

Add a comment |

Stack Exchange Network

Dynamic SQL Server cross tab

2 Answers 2

Hot Network Questions

Dynamic SQL Server cross tab

2 Answers 2

Related

Hot Network Questions