Re Pivoting a table
-
I currently have a table that i want to re pivot from a horizontal layout to a vertical layout.
The source table design is listed below
CREATE TABLE [dbo].[PunchTrends_Chart] (
[FacId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Facility] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Series] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-----Types of Time Card Entry------------------------------------------------
[Smart Scheduler] [decimal](6, 2) NULL ,
[People Editor] [decimal](6, 2) NULL ,
[DCM] [decimal](6, 2) NULL ,
[WFC] [decimal](6, 2) NULL ,
[Timecard Editor] [decimal](6, 2) NULL ,
[MobileTime] [decimal](6, 2) NULL ,
[Group Edit] [decimal](6, 2) NULL ,
[HyperFind] [decimal](6, 2) NULL ,
[Import] [decimal](6, 2) NULL ,
[Labor Activity] [decimal](6, 2) NULL ,
[Employee Maintenance] [decimal](6, 2) NULL ,
[External API] [decimal](6, 2) NULL ,
[Schedule] [decimal](6, 2) NULL ,
[TeleTime] [decimal](6, 2) NULL ,
[WFC Database Manager (Upgrade)] [decimal](6, 2) NULL ,
[TKWeb] [decimal](6, 2) NULL ,
[WFC Web] [decimal](6, 2) NULL ,
[HTML Timecard] [decimal](6, 2) NULL ,
[HTML Timestamp] [decimal](6, 2) NULL ,
[HTML QTS Timestamp] [decimal](6, 2) NULL ,
[Timestamp] [decimal](6, 2) NULL
--------------------------------------------------------------------------------------
) ON [PRIMARY]
GO
The layout of the destination table is below.
CREATE TABLE [dbo].[PunchTrends_CrossTab] (
[FacId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Series] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Totals] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
I've been working all day trying to determine how to re-organize the data from the source table but to no avial.
Basically the concept is as follows:
Each one of the columns in the source table such as DCM, or WFC refer to the type of time card entry. In the source table, the totals for each time card entry are tied to a Facility and Series. I want to flip this around so I have a Facility Id (FacId) related to a set of entry types grouped by the unique Series and then the Totals. The totals for each entry type would be obtained from the value of whatever related column from the source table.
The whole reason for this is so I can return a unique entry description and total to a specific facility by the series.
Any advice is appreciated...
I'm sure who ever answers this post will have questions, but I figured this could get things started.
Galahad
-
I have a ton of questions but then again this will get you started also
Insert into PunchTrends_CrossTab ( FacId, EntryDesc, Series, Totals )
Select FacId, 'DCM' as EntryDesc, Series, SUM(DCM)
From PunchTrends_Chart
where Series = 'X'
UNION ALL
Select FacId, 'WFC' as EntryDesc, Series, SUM(WFC)
From PunchTrends_Chart
where Series = 'Y'
...
* Noel -
What you posted makes sense. I tried it out for those two entry codes WFC & DCM and it seems to work fine, with the exception that I had to put in group by for each select. Please respond w/ your questions and in the meantime, I'm going to try to work your logic into my stored proc. I think I can see how to do this without repeating select statements for each entry code, but we'll see
Series refers to the year/month (2005-05).
I can export both tables as excel sheets if this will help in the process...
Thanks again,
Galahad
-
I've been trying to see how to get this into a stored proc. Basically I think I would need to repeat the select statement for each one of the entry code columns (DCM, WFC) etc. Would it make sense to nest the select in a while loop?
while @cnt < 13 (number of entry code columns)
Select FacId, @EntryCodeVar as EntryDesc, Series, SUM(@EntryCodeVar)
From PunchTrends_Chart
where Series = '2005-05'
group by FacId,WFC,Series
I already have a table that contains all entry codes and descriptions.
EntryCode EntryDesc
A Smart Scheduler
E DCM
I was thinking that I would use this table in conjuction w/ the main table to build the select statements...
What do you think?
Thanks,
Galahad
-
there are several ways to go about this.
If you don't want to hardcode 13 statements with union all and selects (which is not a big deal) then your best bet is to "generate" the statement and use dynamic SQL
ex:
declare @STR varchar(8000)
Set @str = 'Insert into PunchTrends_CrossTab ( FacId, EntryDesc, Series, Totals )'
Select @str = @STR + 'Select FacId , ' + [EntryDesc] + ' as EntryDEsc, Sum( ' + [EntryDesc] + ') Total
From PunchTrends_Chart
where Series = ''2005-05''
group by FacId, ' + EntryDesc + ',Series UNION ALL '
From [YourLookupTable]
set @STR = Left (@str, Len ( @STR 
- 11 ) --remove extra 'union all'exec (@str)
* Noel -
Thanks for the help. I combined your code in my existing sproc which the final version is shown below. I've really learned alot from your advice.
CREATE PROCEDURE [dbo].[spLoadPunchTrends]
@InitLoad as varchar(50)
AS
/***************************************************************************************************************************************************
* Purpose: Builds a select statement to return list of unique punch entry codes for each
facility. The initial one time load inserts data for all Punch Entrys that meet the criteria specified. It
is not limited by a specific. All inserts after the initial load get data for the current month - 1 and
have a @InitLoad flag of Monthly.
* Database: WFC DEV
* Tables affected: PunchTrends_Chart, PunchTrends_CrossTab
*
* History:
* Name Date Version Change
* -------------------------- ---------------------- --------------- ------------------------------------------------------------------------------------------------------------------------
* Galahad Phillips 07/13/2005 1 Created
***************************************************************************************************************************************************/
--complete first insert into PunchTrends_Chart table--------------------------------------------------------------------------------------------------------
declare @chartStr as varchar(8000)
declare @dateClause as varchar(255)
declare @crosstabStr as varchar(8000)
declare @dateLimiter as varchar(255)
set @dateClause=''
set @dateLimiter=''
--limit load to current month-1
if (@InitLoad='Monthly')
set @dateClause=' AND CONVERT(VARCHAR(7),A.PUNCHDTM, 120)= CONVERT(VARCHAR(7),DATEADD(mm,-1,GETDATE()), 120)'
set @dateLimiter =' WHERE Series= CONVERT(VARCHAR(7),DATEADD(mm,-1,GETDATE()), 120)'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--select Facility, District, Division Descriptions------------------------------------------------------------------------------------------------------------------
select @chartStr='SELECT CAST(C.LABORLEV3NM AS NUMERIC(9)) AS FacId,
CAST(C.LABORLEV3DSC + ''('' + C.LABORLEV3NM + '')'' AS VARCHAR(255)) AS Facility,
ISNULL(CAST(C.LABORLEV2DSC + ''('' + C.LABORLEV2NM + '')'' AS VARCHAR(255)),'''') AS District,
ISNULL(CAST(C.LABORLEV1DSC + ''('' + C.LABORLEV1NM + '')'' AS VARCHAR(255)),'''') AS Division'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--format PUNCHDTM to Year-Month (XXXX-XX)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @chartStr=@chartStr+ ',' + 'CONVERT(CHAR(7),A.PUNCHDTM,120) As Series'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--generate percentage of punchs by punch entry type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @chartStr = @chartStr + ',' +'
ROUND(
CAST(
CAST( SUM(CASE WHEN B.FUNCTSTR = ' + '''' + EntryCode+ '''' + ' THEN 1 ELSE 0 END) AS DECIMAL(6,2))/
CAST(COUNT(PUNCHDTM) AS DECIMAL(6,2))
AS DECIMAL(5,4)) *100
,2)
AS [' + CONVERT(VARCHAR(100), EntryDesc) + ']'
FROM PunchEntry_Types_Descriptions
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Finish select statement
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @chartStr = @chartStr + ' FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND B.FUNCTSTR IN (''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'',''L'',''M'',''P'',''S'',''T'',''U'',''X'',''W'',''V'',''Y'',''Z'',''Q'')
AND C.LABORLEV3DSC IS NOT NULL'
select @chartStr = @chartStr + @dateClause + '
GROUP BY
C.LABORLEV3DSC, C.LABORLEV3NM,
C.LABORLEV2DSC, C.LABORLEV2NM,
C.LABORLEV1DSC, C.LABORLEV1NM,
CONVERT(CHAR(7),A.PUNCHDTM,120)
ORDER BY Facility, Series'
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--insert query data into PunchTrends_Chart table
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--insert into PunchTrends_Chart
--exec (@chartStr)
--end first insert---------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--complete second insert into PunchTrends_Crosstab table-------------------------------------------------------------------------------------------
--PunchEntry_Types_Descriptions
set @crosstabStr = 'INSERT INTO PunchTrends_CrossTab (FacId, EntryDesc,Series,Totals)'
select @crosstabStr= @crosstabStr + ' SELECT FacId, ' + '''' + EntryDesc + '''' + ' as EntryDesc, Series, ISNULL(' + '[' + EntryDesc + ']' + ',0) AS Totals
FROM PunchTrends_Chart'
+ @dateLimiter + '
GROUP BY FacId, ' + '[' + EntryDesc + ']' + ', Series UNION ALL'
FROM PunchEntry_Types_Descriptions
--remove extra 'union all'
set @crosstabStr= Left ( @crosstabStr, Len ( @crosstabStr - 11 ))
exec (@crosstabStr)
GO
However I did run into one little problem. The third from the last line where the UNION ALL is stripped off is returning an error. It doesn't make any sense...
Syntax error converting the varchar value "the string"
I did also have a question about the last bit of code you send. I don't understand how simply putting the FROM PunchEntry_Types_Descriptions outside the string makes it loop through the table. Wouldn't you need a select outside of the string in order to loop thru the table?
Thanks again,
Galahad
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply