Need help about Cross Tab - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help about Cross Tab

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-08 : 23:53:52

Hi,

Working with Sqlserver 2008 R2.

Below is my working code for converting rows into columns.


REATE Procedure [dbo].[GetReports]
(
	@Year		int = null,
	@Month	    int = null
)
AS

BEGIN

if OBJECT_ID('#DateRange') is not null drop table #DateRange

 declare @cols nvarchar(4000);
 DECLARE @Query nvarchar(4000);
 DECLARE @fromdate DATE,@todate DATE,@DaysCount int;

 
--set @Month = 8
--set @Year = 2013          



IF(@Year is not null and @Year >0 and @Month is not null and @Month >0)

BEGIN
 SELECT @fromdate = DATEADD(MONTH, (@year - 1900) * 12 + @month - 1, '1900-01-01')
	,@todate = DATEADD(DAY, - 1, DATEADD(MONTH, (@year - 1900) * 12 + @month, '1900-01-01'))
	select @DaysCount = datediff(dd,dateadd(dd, 1-day(@fromdate),@fromdate), dateadd(m,1,dateadd(dd, 1-day(@fromdate),@fromdate)))
	
	
	 select @cols = coalesce(@cols +',','') + '[' + [DATE] + ']' from (

			SELECT	[DATE]	= CONVERT(nVARCHAR(100), DATEADD(DD, n, @fromdate), 121)
			FROM	(
					VALUES (0), (1), (2), (3), (4), (5), (6),(7), (8), (9), (10), (11), (12), (13),
					(14), (15), (16), (17), (18), (19),(20), (21), (22), (23), (24), (25),
					(26), (27), (28), (29), (30),(31)
				) num (n) WHERE n < @DaysCount ) d

END

IF(@month > 0 and @Year > 0 )
 BEGIN
				 ;WITH DateRange(Date) AS
					(
						SELECT
							@fromdate as Date
						UNION ALL
						SELECT
							DATEADD(day, 1, Date) as Date
						FROM
							DateRange
						WHERE
							Date <= @todate
					)
				    
				SELECT date into #DateRange from DateRange
				 
				 set @Query = 'SELECT * from
								 ( 			 
				 select Name as [Name],mydate as [Date_Of_Day] ,COALESCE ( cast(Records as varchar) , cast((cast(newdate as date)) as varchar)) as [Records] from(   
					SELECT c.Name,Date as mydate,c.GrocessaryStoredelay as delay  
					,case when b.Records is null then DATEADD(DAY, (nullif(c.GrocessaryStoreDelay,0) + nullif(DataFrequency,0)), a.Date) else convert(datetime,0,101) end as newdate,b.Records
					FROM #DateRange a
				Cross join
				dbo.GrocessaryStore c
				Left join
				dbo.Month_Report b
				On c.StoreID = b.StoreID
				And a.date = b.Date_Of_Day
				) aa where aa.delay is not null) 
								  as p PIVOT ( max([records]) FOR [Date_Of_Day] 
											  IN ('+ @cols+')) AS pvt  order by pvt.Name' 			

END

                    
Print @Query;
EXEC sp_executesql @Query 

END

Is is possible to achieve this with Cross Tabs? I am learning Pivot Vs Cross Tab for the performance.
But on my sample i am struggling to implement cross tab. Can any one suggest me is it possible to create Cross Tab on my sample. If yes please post some modified query based on my sample

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-09 : 12:31:59

Sample Sta and Expected Result:


Table: GrocessaryStore  
IdGrocessaryStore int identity(1,1) Primary key,
Name varchar(50),GrocessaryStoreDelay int, DataFrequency int, StoreID varchar(20)

With GrocessaryStore  as (
select 1 as IdGrocessaryStore,'WallMart' as Name,10 as GrocessaryStoreDelay, 2 as DataFrequency, 100 as StoreID union all
select 2 as IdGrocessaryStore,'Kelly' as Name,13 as GrocessaryStoreDelay, 1 as DataFrequency, 101 as StoreID union all
select 3 as IdGrocessaryStore,'Subway' as Name,12 as GrocessaryStoreDelay, 3 as DataFrequency, 102 as StoreID union all
select 4 as IdGrocessaryStore,'Dominos' as Name,14 as GrocessaryStoreDelay, 5 as DataFrequency, 103 as StoreID union all
select 5 as IdGrocessaryStore,'Pizzahut' as Name,15 as GrocessaryStoreDelay, 3 as DataFrequency, 104 as StoreID union all
select 6 as IdGrocessaryStore,'BigY' as Name,12 as GrocessaryStoreDelay, 8 as DataFrequency, 105 as StoreID union all
select 7 as IdGrocessaryStore,'Target' as Name,19 as GrocessaryStoreDelay, 3 as DataFrequency, 106 as StoreID union all
select 8 as IdGrocessaryStore,'FishingNet' as Name,20 as GrocessaryStoreDelay, 14 as DataFrequency, 107 as StoreID )

Table : Month_Report

IDReport int identity(1,1) primary key,StoreID varchar(20),Date_Of_Day date, Records int

with  Month_Report as (
 select 1 as IDReport, 100 as StoreID,'2014-02-01' as Date_Of_Day,1500 as Records union all
 select 2 as IDReport, 100 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
 select 3 as IDReport, 100 as StoreID,'2014-02-03' as Date_Of_Day,1300 as Records union all
 select 4 as IDReport, 100 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
 select 5 as IDReport, 100 as StoreID,'2014-02-05' as Date_Of_Day,1100 as Records union all
 select 6 as IDReport, 100 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
 select 7 as IDReport, 100 as StoreID,'2014-02-07' as Date_Of_Day,1200 as Records union all
 select 8 as IDReport, 100 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
 select 9 as IDReport, 100 as StoreID,'2014-02-09' as Date_Of_Day,1800 as Records union all
 select 10 as IDReport, 100 as StoreID,'2014-02-10' as Date_Of_Day,1900 as Records union all
 select 11 as IDReport, 100 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
 select 12 as IDReport, 100 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
 select 13 as IDReport, 100 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
 select 14 as IDReport, 100 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
 select 15 as IDReport, 100 as StoreID,'2014-02-15' as Date_Of_Day,NULL as Records union all   
 select 16 as IDReport, 100 as StoreID,'2014-02-16' as Date_Of_Day, 1000 as Records union all 
  select 17 as IDReport, 101 as StoreID,'2014-02-01' as Date_Of_Day,2500 as Records union all
 select 18 as IDReport, 101 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
 select 19 as IDReport, 101 as StoreID,'2014-02-03' as Date_Of_Day,NULL as Records union all
 select 20 as IDReport, 101 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
 select 21 as IDReport, 101 as StoreID,'2014-02-05' as Date_Of_Day,2100 as Records union all
 select 22 as IDReport, 101 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
 select 23 as IDReport, 101 as StoreID,'2014-02-07' as Date_Of_Day,2200 as Records union all
 select 24 as IDReport, 101 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
 select 25 as IDReport, 101 as StoreID,'2014-02-09' as Date_Of_Day,3800 as Records union all
 select 26 as IDReport, 101 as StoreID,'2014-02-10' as Date_Of_Day,3900 as Records union all
 select 27 as IDReport, 101 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
 select 28 as IDReport, 101 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
 select 29 as IDReport, 101 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
 select 30 as IDReport, 101 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
 select 31 as IDReport, 101 as StoreID,'2014-02-15' as Date_Of_Day,1600 as Records union all 
 select 32 as IDReport, 101 as StoreID,'2014-02-16' as Date_Of_Day,1800 as Records union all
 select 33 as IDReport, 101 as StoreID,'2014-02-17' as Date_Of_Day,2700 as Records union all
 select 34 as IDReport, 101 as StoreID,'2014-02-18' as Date_Of_Day,2600 as Records 
 
)

Please execute the below two select queries and combine them as expected result. The output is two rows. Hope my sample is clear.


Expected Result:


select 'WallMart' as Name,1500 as [2014-02-01],1200 as [2014-02-02],1300 as [2014-02-03],1800 as [2014-02-04],1100 as [2014-02-05],1000 as [2014-02-06],1200 as [2014-02-07],
1400 as [2014-02-08],1800 as [2014-02-09],1900 as [2014-02-10],1700 as [2014-02-11],1000 as [2014-02-12],1200 as [2014-02-13],1700 as [2014-02-14],
'2014-02-27' as [2014-02-15],1000 as [2014-02-16],
'2014-03-01' as [2014-02-17],'2014-03-02' as [2014-02-18],'2014-03-03' as [2014-02-19],'2014-03-04' as [2014-02-20],'2014-03-02' as [2014-02-21],'2014-03-06' as [2014-02-22],'2014-03-07' as [2014-02-23],
'2014-03-08' as [2014-02-24],'2014-03-09' as [2014-02-25],'2014-03-10' as [2014-02-26],'2014-03-11' as [2014-02-27],'2014-03-12' as [2014-02-28]

select 'Kelly' as Name,2500 as [2014-02-01],1200 as [2014-02-02],'2014-02-17' as [2014-02-03],1800 as [2014-02-04],2100 as [2014-02-05],1000 as [2014-02-06],2200 as [2014-02-07],
1400 as [2014-02-08],3800 as [2014-02-09],3900 as [2014-02-10],1700 as [2014-02-11],1000 as [2014-02-12],1200 as [2014-02-13],1700 as [2014-02-14],
1600 as [2014-02-15],1800 as [2014-02-16],
2700 as [2014-02-17],2600 as [2014-02-18],'2014-03-05' as [2014-02-19],'2014-03-06' as [2014-02-20],'2014-03-07' as [2014-02-21],'2014-03-08' as [2014-02-22],'2014-03-09' as [2014-02-23],
'2014-03-10' as [2014-02-24],'2014-03-11' as [2014-02-25],'2014-03-12' as [2014-02-26],'2014-03-13' as [2014-02-27],'2014-03-14' as [2014-02-28]

As i said the The proc i posted on my previous post was working fine. Thought of achieving that through Cross Tabs which will help me to learn about it. please help me on this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-03-10 : 16:05:04

You can make the procedure much more efficient by NOT CROSS JOINING...

CREATE PROCEDURE dbo.GetReports
(
	@Year SMALLINT = NULL,
	@Month TINYINT = NULL
)
AS

SET NOCOUNT ON;

IF @Year IS NULL OR @Year NOT BETWEEN 1900 AND 9999
	RETURN

IF @Month IS NULL OR @Month NOT BETWEEN 1 AND 12
	RETURN

DECLARE @FromDate DATETIME = DATEADD(MONTH, 12 * @Year - 22801 + @Month, '19000101'),
	@ToDate DATETIME = DATEADD(MONTH, 12 * @Year - 22800 + @Month, '18991231'),
	@SQL VARCHAR(MAX);

-- Get the data more efficient
SELECT		c.Name AS theStore,
		CAST(b.Date_Of_Day AS DATE) AS theDate,
		MAX(CASE
			WHEN b.Records IS NOT NULL THEN CAST(b.Records AS VARCHAR(100))
			ELSE CONVERT(CHAR(8), DATEADD(DAY, c.GrocessaryStoreDelay + NULLIF(c.DataFrequency, 0), b.Date_Of_Day), 112)
		END) AS theValue
INTO		#Data
FROM		dbo.GrocessaryStore AS c
LEFT JOIN	dbo.Month_Report AS b ON b.StoreID = c.StoreID
			AND b.Date_Of_Day BETWEEN @FromDate AND @ToDate
WHERE		c.GrocessaryStoreDelay > 0
GROUP BY	c.Name,
		b.Date_Of_Day;

-- Do the cross tab
SET	@SQL = 'SELECT theStore AS Name';

SET	@SQL +=	(
			SELECT		',MAX(CASE WHEN theDate = ' + QUOTENAME(theDate, '''') + ' THEN theValue ELSE '''' END) AS ' + QUOTENAME(theDate)
			FROM		(
						SELECT	CONVERT(CHAR(8), DATEADD(DAY, Number, @FromDate), 112) AS theDate,
							Number
						FROM	master.dbo.spt_values
						WHERE	[type] = 'P'
							AND Number < DAY(@ToDate)		
					) AS d
			ORDER BY	Number
			FOR XML		PATH('')
		);

SET	@SQL += ' FROM #Data GROUP BY theStore ORDER BY theStore;';

EXEC	(@SQL);
GO

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-10 : 20:17:06

Hi SwePeso,

Thank you so much for your time on this. I tried to create the proc as you suggested and ran the proc. But it shows empty data for the dates column if there is no records. But as per my logic it should calculate the delay + frequency and to be added with date column. But it's not happening. Are we missing something? please suggest me

If you could execute the expected result of my sample if it has records it will show the records else it will add the date(from column)+ delay + frequency and will be displayed on the row cell.

also getting warning message as
Warning: Null value is eliminated by an aggregate or other SET operation.

Please help me on this

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-11 : 09:22:44

Hi Swepeso,

The only place i am struck up with on the sample is


SET	@SQL +=	(
			SELECT		',MAX(CASE WHEN theDate = ' + QUOTENAME(theDate, '''') + ' THEN theValue ELSE 0 END) AS ' + QUOTENAME(theDate)
			FROM		(
						SELECT	CONVERT(CHAR(8), DATEADD(DAY, Number, @FromDate), 112) AS theDate,
							Number
						FROM	master.dbo.spt_values
						WHERE	[type] = 'P'
							AND Number < DAY(@ToDate)		
					) AS d
			ORDER BY	Number
			FOR XML		PATH('')
		);

On the case instead of 0 i want to display the calculated date logic


DATEADD(DAY, (nullif(c.GrocessaryStoreDelay,0) + nullif(DataFrequency,0)), a.Date) else convert(datetime,0,101)

But if i add this i am getting error. Any suggestion or changes on the code please guide me

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-11 : 21:27:29

This is how achieved.

Thanks Peso for your help.,


CREATE TABLE #GrocessaryStore 
(  
    IdGrocessaryStore int Primary key
    ,Name varchar(50)
    ,GrocessaryStoreDelay int
    ,DataFrequency int
    ,StoreID varchar(20)
);    

With GrocessaryStore  as 
(
    select 1 as IdGrocessaryStore,'WallMart' as Name,10 as GrocessaryStoreDelay, 2 as DataFrequency, 100 as StoreID union all
    select 2 as IdGrocessaryStore,'Kelly' as Name,13 as GrocessaryStoreDelay, 1 as DataFrequency, 101 as StoreID union all
    select 3 as IdGrocessaryStore,'Subway' as Name,12 as GrocessaryStoreDelay, 3 as DataFrequency, 102 as StoreID union all
    select 4 as IdGrocessaryStore,'Dominos' as Name,14 as GrocessaryStoreDelay, 5 as DataFrequency, 103 as StoreID union all
    select 5 as IdGrocessaryStore,'Pizzahut' as Name,15 as GrocessaryStoreDelay, 3 as DataFrequency, 104 as StoreID union all
    select 6 as IdGrocessaryStore,'BigY' as Name,12 as GrocessaryStoreDelay, 8 as DataFrequency, 105 as StoreID union all
    select 7 as IdGrocessaryStore,'Target' as Name,19 as GrocessaryStoreDelay, 3 as DataFrequency, 106 as StoreID union all
    select 8 as IdGrocessaryStore,'FishingNet' as Name,20 as GrocessaryStoreDelay, 14 as DataFrequency, 107 as StoreID 
)
INSERT INTO #GrocessaryStore
SELECT * FROM GrocessaryStore;

CREATE TABLE #Month_Report
(
    IDReport int  primary key
    ,StoreID varchar(20)
    ,Date_Of_Day date
    , Records int
);

WITH Month_Report as 
(
    select 1 as IDReport, 100 as StoreID,'2014-02-01' as Date_Of_Day,1500 as Records union all
    select 2 as IDReport, 100 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
    select 3 as IDReport, 100 as StoreID,'2014-02-03' as Date_Of_Day,1300 as Records union all
    select 4 as IDReport, 100 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
    select 5 as IDReport, 100 as StoreID,'2014-02-05' as Date_Of_Day,1100 as Records union all
    select 6 as IDReport, 100 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
    select 7 as IDReport, 100 as StoreID,'2014-02-07' as Date_Of_Day,1200 as Records union all
    select 8 as IDReport, 100 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
    select 9 as IDReport, 100 as StoreID,'2014-02-09' as Date_Of_Day,1800 as Records union all
    select 10 as IDReport, 100 as StoreID,'2014-02-10' as Date_Of_Day,1900 as Records union all
    select 11 as IDReport, 100 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
    select 12 as IDReport, 100 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
    select 13 as IDReport, 100 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
    select 14 as IDReport, 100 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
    select 15 as IDReport, 100 as StoreID,'2014-02-15' as Date_Of_Day,NULL as Records union all   
    select 16 as IDReport, 100 as StoreID,'2014-02-16' as Date_Of_Day, 1000 as Records union all 
    select 17 as IDReport, 101 as StoreID,'2014-02-01' as Date_Of_Day,2500 as Records union all
    select 18 as IDReport, 101 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
    select 19 as IDReport, 101 as StoreID,'2014-02-03' as Date_Of_Day,NULL as Records union all
    select 20 as IDReport, 101 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
    select 21 as IDReport, 101 as StoreID,'2014-02-05' as Date_Of_Day,2100 as Records union all
    select 22 as IDReport, 101 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
    select 23 as IDReport, 101 as StoreID,'2014-02-07' as Date_Of_Day,2200 as Records union all
    select 24 as IDReport, 101 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
    select 25 as IDReport, 101 as StoreID,'2014-02-09' as Date_Of_Day,3800 as Records union all
    select 26 as IDReport, 101 as StoreID,'2014-02-10' as Date_Of_Day,3900 as Records union all
    select 27 as IDReport, 101 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
    select 28 as IDReport, 101 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
    select 29 as IDReport, 101 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
    select 30 as IDReport, 101 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
    select 31 as IDReport, 101 as StoreID,'2014-02-15' as Date_Of_Day,1600 as Records union all 
    select 32 as IDReport, 101 as StoreID,'2014-02-16' as Date_Of_Day,1800 as Records union all
    select 33 as IDReport, 101 as StoreID,'2014-02-17' as Date_Of_Day,2700 as Records union all
    select 34 as IDReport, 101 as StoreID,'2014-02-18' as Date_Of_Day,2600 as Records 
)
INSERT INTO #Month_Report
SELECT * FROM Month_Report;

DECLARE @Year INT = 2014
    ,@Month INT = 02;

DECLARE @FromDate DATETIME = DATEADD(month, @Month-1, DATEADD(year, @year-1900, 0)),
	@ToDate DATETIME = DATEADD(month, @Month, DATEADD(year, @year-1900, 0))-1;

DECLARE @SQL NVARCHAR(MAX)
    ,@SQLParms NVARCHAR(MAX) = '@FromDate DATE, @ToDate DATE';

WITH Dates AS
(
    SELECT d=CONVERT(CHAR(10), @FromDate + n, 120)
    FROM 
    (
        SELECT n=0 UNION ALL 
        SELECT TOP (DATEDIFF(day, @FromDate, @ToDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES(0),(0),(0),(0)) a (n1)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) b(n2)
    ) a
)
SELECT @SQL = N'
SELECT Name' + 
(
    SELECT ', [' + d + ']=ISNULL(CAST(MAX(CASE WHEN Date_Of_Day=''' + d +''' THEN CAST(Records AS VARCHAR(10)) END) AS VARCHAR(10)), ' +
        'CONVERT(VARCHAR(10), DATEADD(day, MAX(GrocessaryStoreDelay) + MAX(DataFrequency), ''' + d + '''),120))'
    FROM Dates
    ORDER BY d
    FOR XML PATH(''), TYPE 
).value('.', 'VARCHAR(MAX)') +
N'FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
WHERE Date_of_Day BETWEEN @FromDate AND @ToDate
GROUP BY Name;';

PRINT @SQL;
EXEC sp_executesql @SQL, @SQLParms, @FromDate=@FromDate, @ToDate=@ToDate;

GO
DROP TABLE #GrocessaryStore;
DROP TABLE #Month_Report;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources