October 14, 2008 at 4:56 am
Hi All
i have this stored procedure:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[selectReportAttendance]
@entitymode VARCHAR(50),
@periodmode VARCHAR(50),
@wherestring VARCHAR(500),
@userid VARCHAR(36)
AS
BEGIN
DECLARE @ContextID VARCHAR(100)
DECLARE @id VARCHAR(50)
SET @id = ( SELECT ISNULL(MAX(id), 0) + 1
FROM ReportsDailyAttendanceWorkingTable
)
SET @ContextID = ( SELECT ( @userid + ':' + @entitymode + ':'
+ @periodmode + ':'
+ CONVERT(VARCHAR(50), GETDATE(), 111)
+ ':' + @id )
)
EXECUTE ( '
INSERT INTO [ReportsDailyAttendanceWorkingTable]
([Sunday]
,[Saturday]
,[Friday]
,[Thursday]
,[Wednesday]
,[Tuesday]
,[Monday]
,[AgentID]
,[AgentName]
,[TeamLeaderName]
,[TeamName]
,[AgentLevelID]
,[DivisionID]
,[ClientID]
,[CampaignID]
,[LoggedDate]
,[CampaignTeamID]
,[Hours]
,[ContextID])
select [Sunday]
,[Saturday]
,[Friday]
,[Thursday]
,[Wednesday]
,[Tuesday]
,[Monday]
,[AgentID]
,[AgentName]
,[TeamLeaderName]
,[TeamName]
,[AgentLevelID]
,[DivisionID]
,[ClientID]
,[CampaignID]
,convert(varchar(50),loggeddate, 111) AS [LoggedDate]
,[CampaignTeamID]
,[Hours]
, ''' + @ContextID + '''
FROM [vw_ReportDailyAttendance]
' + @wherestring
)
DECLARE @attendance TABLE
(
id BIGINT IDENTITY(1, 1)
NOT NULL,
[Sunday] VARCHAR(50),
[Saturday] VARCHAR(50),
[Friday] VARCHAR(50),
[Thursday] VARCHAR(50),
[Wednesday] VARCHAR(50),
[Tuesday] VARCHAR(50),
[Monday] VARCHAR(50),
[AgentID] UNIQUEIDENTIFIER,
[AgentName] VARCHAR(50),
[TeamLeaderName] VARCHAR(50),
[TeamName] VARCHAR(50),
[AgentLevelID] BIGINT,
[DivisionID] BIGINT,
[ClientID] BIGINT,
[CampaignID] BIGINT,
[LoggedDate] VARCHAR(50),
[CampaignTeamID] BIGINT,
[Hours] DECIMAL(18, 2)
)
-- DECLARE @ldmonday VARCHAR(50)
-- DECLARE @ldtuesday VARCHAR(50)
-- DECLARE @ldwednesday VARCHAR(50)
-- DECLARE @ldthursday VARCHAR(50)
-- DECLARE @ldfriday VARCHAR(50)
-- DECLARE @ldsaturday VARCHAR(50)
-- DECLARE @ldsunday VARCHAR(50)
DECLARE @uniqueagents TABLE
(
id BIGINT IDENTITY(1, 1)
NOT NULL,
agentid UNIQUEIDENTIFIER NOT NULL
)
INSERT INTO @uniqueagents ( agentid )
SELECT DISTINCT
agentid
FROM [ReportsDailyAttendanceWorkingTable]
DECLARE @acounter BIGINT
SET @acounter = 1
WHILE @acounter <= ( SELECT MAX(id)
FROM @uniqueagents
)
BEGIN
DECLARE @agentid UNIQUEIDENTIFIER
SET @agentid = ( SELECT agentid
FROM @uniqueagents
WHERE id = @acounter
)
DECLARE @indvidualattendance TABLE
(
id BIGINT IDENTITY(1, 1)
NOT NULL,
[Sunday] VARCHAR(50),
[Saturday] VARCHAR(50),
[Friday] VARCHAR(50),
[Thursday] VARCHAR(50),
[Wednesday] VARCHAR(50),
[Tuesday] VARCHAR(50),
[Monday] VARCHAR(50),
[AgentID] UNIQUEIDENTIFIER,
[AgentName] VARCHAR(50),
[TeamLeaderName] VARCHAR(50),
[TeamName] VARCHAR(50),
[AgentLevelID] BIGINT,
[DivisionID] BIGINT,
[ClientID] BIGINT,
[CampaignID] BIGINT,
[LoggedDate] VARCHAR(50),
[CampaignTeamID] BIGINT,
[Hours] DECIMAL(18, 2),
[ContextID] VARCHAR(100)
)
DECLARE @counter TINYINT
DECLARE @monday VARCHAR(50)
DECLARE @tuesday VARCHAR(50)
DECLARE @wednesday VARCHAR(50)
DECLARE @thursday VARCHAR(50)
DECLARE @friday VARCHAR(50)
DECLARE @saturday VARCHAR(50)
DECLARE @sunday VARCHAR(50)
DECLARE @totalhours DECIMAL(18, 2)
SET @totalhours = 0
SET @counter = 1
WHILE @counter < 8
BEGIN
DELETE FROM @indvidualattendance
INSERT INTO @indvidualattendance
(
[Sunday],
[Saturday],
[Friday],
[Thursday],
[Wednesday],
[Tuesday],
[Monday],
[AgentID],
[AgentName],
[TeamLeaderName],
[TeamName],
[AgentLevelID],
[DivisionID],
[ClientID],
[CampaignID],
[LoggedDate],
[CampaignTeamID],
[Hours],
[ContextID]
)
SELECT [Sunday],
[Saturday],
[Friday],
[Thursday],
[Wednesday],
[Tuesday],
[Monday],
[AgentID],
[AgentName],
[TeamLeaderName],
[TeamName],
[AgentLevelID],
[DivisionID],
[ClientID],
[CampaignID],
[LoggedDate],
[CampaignTeamID],
[Hours],
[ContextID]
FROM [ReportsDailyAttendanceWorkingTable]
WHERE agentid = @agentid
AND ContextID = @contextid
IF @counter = 1
BEGIN
-- SET @ldmonday = ( SELECT LoggedDate
-- FROM @indvidualattendance
-- WHERE monday IS NOT NULL
-- )
SET @monday = ( SELECT Monday
FROM @indvidualattendance
WHERE monday IS NOT NULL
)
SET @totalhours = @totalhours
+ ISNULL(( SELECT hours
FROM @indvidualattendance
WHERE monday = 'Present'
), 0)
END
IF @counter = 2
BEGIN
-- SET @ldtuesday = ( SELECT LoggedDate
-- FROM @indvidualattendance
-- WHERE tuesday IS NOT NULL
-- )
SET @tuesday = ( SELECT tuesday
FROM @indvidualattendance
WHERE tuesday IS NOT NULL
)
SET @totalhours = @totalhours
+ ISNULL(( SELECT hours
FROM @indvidualattendance
WHERE tuesday = 'Present'
), 0)
END
IF @counter = 3
BEGIN
-- SET @ldwednesday = ( SELECT LoggedDate
-- FROM @indvidualattendance
-- WHERE Wednesday IS NOT NULL
-- )
SET @wednesday = ( SELECT wednesday
FROM @indvidualattendance
WHERE wednesday IS NOT NULL
)
SET @totalhours = @totalhours
+ ISNULL(( SELECT hours
FROM @indvidualattendance
WHERE wednesday = 'Present'
), 0)
END
IF @counter = 4
BEGIN
-- SET @ldthursday = ( SELECT LoggedDate
-- FROM @indvidualattendance
-- WHERE Thursday IS NOT NULL
-- )
SET @thursday = ( SELECT thursday
FROM @indvidualattendance
WHERE thursday IS NOT NULL
)
SET @totalhours = @totalhours
+ ISNULL(( SELECT hours
FROM @indvidualattendance
WHERE thursday = 'Present'
), 0)
END
IF @counter = 5
BEGIN
-- SET @ldfriday = ( SELECT LoggedDate
-- FROM @indvidualattendance
-- WHERE Friday IS NOT NULL
-- )
SET @friday = ( SELECT friday
FROM @indvidualattendance
WHERE friday IS NOT NULL
)
SET @totalhours = @totalhours
+ ISNULL(( SELECT hours
FROM @indvidualattendance
WHERE friday = 'Present'
), 0)
END
IF @counter = 6
BEGIN
-- SET @ldsaturday = ( SELECT LoggedDate
-- FROM @indvidualattendance
-- WHERE Saturday IS NOT NULL
-- )
SET @saturday = ( SELECT Saturday
FROM @indvidualattendance
WHERE Saturday IS NOT NULL
)
SET @totalhours = @totalhours
+ ISNULL(( SELECT hours
FROM @indvidualattendance
WHERE Saturday = 'Present'
), 0)
END
IF @counter = 7
BEGIN
-- SET @ldsunday = ( SELECT LoggedDate
-- FROM @indvidualattendance
-- WHERE Sunday IS NOT NULL
-- )
SET @sunday = ( SELECT sunday
FROM @indvidualattendance
WHERE sunday IS NOT NULL
)
SET @totalhours = @totalhours
+ ISNULL(( SELECT hours
FROM @indvidualattendance
WHERE sunday = 'Present'
), 0)
END
SET @counter = @counter + 1
END
INSERT INTO @attendance
(
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday],
[Sunday],
[AgentID],
[AgentName],
[TeamLeaderName],
[TeamName],
[AgentLevelID],
[DivisionID],
[ClientID],
[CampaignID],
[CampaignTeamID],
[Hours],
[LoggedDate]
)
SELECT DISTINCT
@monday AS Monday,
@tuesday AS Tuesday,
@wednesday AS Wednesday,
@thursday AS Thursday,
@friday AS Friday,
@saturday AS Saturday,
@sunday AS Sunday,
[AgentID],
[AgentName],
[TeamLeaderName],
[TeamName],
[AgentLevelID],
[DivisionID],
[ClientID],
[CampaignID],
[CampaignTeamID],
@totalhours,
CONVERT(VARCHAR(50), [LoggedDate], 111)
FROM @indvidualattendance
SET @acounter = @acounter + 1
END
SELECT DISTINCT
[AgentName],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday],
[Sunday],
[Hours]
FROM @attendance
END
it is quite long and complicated and produces an accurate result.
such as:
Agent NameMondayTuesdayWednesdayThursdayFridaySaturdaySunday
Emil SookalooPresentAnnual LeaveUnknownUnknownUnknown
Irshaad KhanPresentAnnual LeaveUnknownUnknownUnknown
now
i want a result like this:
Agent NameMonday 2008/20/13Tuesday 2008/10/14WednesdayThursdayFriday 2008/10/10Saturday 2008/10/11Sunday 2008/10/12
Emil SookalooPresentAnnual LeaveUnknownUnknownUnknown
Irshaad KhanPresentAnnual LeaveUnknownUnknownUnknown
As you can see i have commented varialbles in my sp, namely the ones that start with @Ld.
my intension is to do this in the final select statement:
SELECT DISTINCT
[AgentName],
[Monday] as [Monday + ' ' + @ldmonday] ,
[Tuesday] as [Tuesday + ' ' + @ldtuesday],
[Wednesday] as [Wednesday + ' ' + @ldwednesday],
[Thursday] as [Thursday + ' ' + @ldthursday],
[Friday] as [Friday + ' ' + @ldfriday],
[Saturday] as [Saturday + ' ' + @ldfriday],
[Sunday] as [Sunday + ' ' + @ldsunday],
[Hours]
FROM @attendance
but this produces column names that are literally [Friday + ' ' + @ldfriday] for example.
i have tried this : [Friday] as @ldfriday
but it produces a syntax error.
so my question is: HOW DO I DYNAMICALLY GENERATE COLUMN NAMES USING TABLE DATA?
can someone help?
Chris Morton
October 15, 2008 at 8:10 am
In order to dynamically generate column names, you need to have dynamically generated SQL.
Declare @sql varchar(max)
Declare @col1Name varchar(50), ...
Set @sql = 'Select Monday as ' + @col1Name + ' From ...'
exec sp_executesql @sql
Just out of curiosity, though, usually something like this is better done in whatever UI is going to be displaying the results.
October 22, 2008 at 4:53 am
Hi
Thanks for your reply
to satisfy your curiousity this is displayed in a web application, BUT, the clients also include windows mobile apps and windows apps.
the datagrid that displays this data is one of thos irritating 'autogenerate column names' ones and it displays results from a number of different stored procedures.
that is why i need to generate the column names dynamically.
thanks!
Chris
October 23, 2008 at 8:26 am
This sounds like a situation where if you know the date (from the data) of the first day of the week, then you can generate a list of column names quite easily using a "tally table" whose only contents are the numbers from 1 to 7.
DECLARE @TALLY TABLE (RN int PRIMARY KEY CLUSTERED, WD varchar(9))
INSERT INTO @TALLY (RN, WD)
SELECT 1, 'Monday' UNION ALL
SELECT 2, 'Tuesday' UNION ALL
SELECT 3, 'Wednesday' UNION ALL
SELECT 4, 'Thursday' UNION ALL
SELECT 5, 'Friday' UNION ALL
SELECT 6, 'Saturday' UNION ALL
SELECT 7, 'Sunday'
DECLARE @BEGIN_DATE AS DateTime, @COLUMN_LIST AS varchar(200)
SET @BEGIN_DATE = '10/13/2008'
SET @COLUMN_LIST = ''
SELECT @COLUMN_LIST = @COLUMN_LIST +
'[' + WD + ' ' + CAST(YEAR(DATEADD(d,RN,@BEGIN_DATE)) AS char(4)) + '/' +
RIGHT('0' + CAST(MONTH(DATEADD(d,RN,@BEGIN_DATE)) AS varchar(2)),2) + '/' +
RIGHT('0' + CAST(DAY(DATEADD(d,RN,@BEGIN_DATE)) AS varchar(2)),2) +
CASE RN WHEN 7 THEN '] ' ELSE '], ' END
FROM @TALLY
SELECT @COLUMN_LIST
The above code can be adapted to allow you to specify where @BEGIN_DATE comes from.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply