asked
i want to pivot table dynamically
DECLARE @SupervisorId varchar(50) DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SET @SupervisorId=312609 SET @StartDate='1-jan-2014' SET @EndDate='10-jan-2014' SELECT * FROM ( SELECT tblAccessInfo.Associate_ID AS Associate_ID , convert(varchar(10),tblAccessInfo.date,101) as DateSwiped, convert(varchar(10),tblAccessInfo.FirstLoginTime,108) as FirstLoginTime, convert(varchar(10),tblAccessInfo.LastLogoutTime,108) as LastLogoutTime, --convert(float, convert(varchar,datepart(hour,tblAccessInfo.LastLogoutTime - FirstLoginTime) + convert(float,datepart(minute,tblAccessInfo.LastLogoutTime - FirstLoginTime))/60)) as TotalAvailableTime AvailableTime as TotalAvailableTime --, convert(varchar(10),tblAccessInfo.EffectiveWorkedTime,108) as EffectiveWorkedTime from tblAccesscardDetails tblAccessInfo, tblAssociate tblAssociate where tblAssociate.Supervisor_ID IN (@SupervisorId) and tblAssociate.Associate_ID = tblAccessInfo.Associate_ID and Date BETWEEN @StartDate AND @EndDate) As original PIVOT ( SUM(TotalAvailableTime) FOR DateSwiped IN ([1-jan-2014],[2-jan-2014]) ) AS PivotTable want to pivot table dynamically using DateSwiped field
Well, I am not looking at your data or field names; but here is how you can do it. Proof of concept below. USE [D_DP_Adhoc] GO -- Create Sample data -- This section is just used for testing and proof of concept -------------------------------------------------------------------------------- WITH test_data(id, Category, Amount) AS ( SELECT 1, 'Cat A', 1 UNION ALL SELECT 1, 'Cat A', 2 UNION ALL SELECT 1, 'Cat A', 3 UNION ALL SELECT 1, 'Cat B', 4 UNION ALL SELECT 1, 'Cat B', 5 UNION ALL SELECT 2, 'Cat B', 6 UNION ALL SELECT 2, 'Cat B', 7 UNION ALL SELECT 2, 'Cat C', 8 UNION ALL SELECT 3, 'Cat C', 9 UNION ALL SELECT 1, 'Cat A', 1 ) SELECT * INTO #test FROM test_data; -- Review Test data SELECT * FROM #test; -- Config Dynamic PIVOT -------------------------------------------------------------------------------- DECLARE @table VARCHAR(128) = '#test' -- Full address Table Name (use proper syntax) , @field_transpose VARCHAR(32) = 'id' -- Field in Table to Transpose (Vertical to Horizontal) , @field_amount VARCHAR(32) = 'Amount' -- Field in Table to Summarize ; -- Environment Setup / Internal Use Variables -------------------------------------------------------------------------------- DECLARE @sql VARCHAR(max) = null; DECLARE @csv VARCHAR(max) = null; IF OBJECT_ID('tempdb..##output', 'U') IS NOT NULL BEGIN DROP TABLE ##output; END -- Get CSV Field Values (for FOR) -------------------------------------------------------------------------------- SET @sql = ' DECLARE @csv VARCHAR(max) = ''''; --MySQL Group_concat() simulation SELECT @csv = @csv + quotename(' + @field_transpose + ') + '','' FROM ' + @table + ' GROUP BY [' + @field_transpose + '] ORDER BY [' + @field_transpose + '] -- Remove trailing comma and store value for use outside current scope SELECT SUBSTRING(@csv, 1, LEN(@csv) - 1) AS [csv] INTO ##output; '; EXEC (@sql); -- Save list of Values SELECT @csv = [csv] FROM ##output; -- PRINT @csv; -- Create Dynamic PIVOT -------------------------------------------------------------------------------- SET @sql = ' SELECT * FROM ' + @table + ' PIVOT ( Sum([' + @field_amount + ']) FOR [' + @field_transpose + '] IN (' + @csv + ') ) AS PivotTable '; EXEC (@sql); -- Maintennance -------------------------------------------------------------------------------- DROP TABLE #test; DROP TABLE ##output; ![alt text][1] [1]: /storage/temp/1628-untitled.jpg
15 People are following this question.
