PIVOT SQL question 1

[psimon1]

Hello

I am having a hard time with SQL Server 2005's pivot command and would like to know what I'm screwing up. I created this view with the intent of pivoting on it. This view works fine:

ALTER VIEW vwPAYROLL1
AS SELECT LAWAPP.QUARTWAGE.COMPANY, LAWAPP.EMPLOYEE.LAST_NAME, LAWAPP.EMPLOYEE.FIRST_NAME, LAWAPP.QUARTWAGE.EMPLOYEE,
LAWAPP.QUARTWAGE.PAYROLL_YEAR, LAWAPP.QUARTWAGE.PAY_SUM_GRP, LAWAPP.PAYSUMGRP.DESCRIPTION,
SUM(LAWAPP.QUARTWAGE.WAGE_AMOUNT) AS TOT_WAGES
FROM LAWAPP.QUARTWAGE INNER JOIN
LAWAPP.PAYSUMGRP ON LAWAPP.QUARTWAGE.COMPANY = LAWAPP.PAYSUMGRP.COMPANY AND
LAWAPP.QUARTWAGE.PAY_SUM_GRP = LAWAPP.PAYSUMGRP.PAY_SUM_GRP INNER JOIN
LAWAPP.EMPLOYEE ON LAWAPP.QUARTWAGE.COMPANY = LAWAPP.EMPLOYEE.COMPANY AND
LAWAPP.QUARTWAGE.EMPLOYEE = LAWAPP.EMPLOYEE.EMPLOYEE
WHERE LAWAPP.QUARTWAGE.PAYROLL_YEAR = 2007
GROUP BY LAWAPP.QUARTWAGE.COMPANY, LAWAPP.EMPLOYEE.LAST_NAME, LAWAPP.EMPLOYEE.FIRST_NAME, LAWAPP.QUARTWAGE.EMPLOYEE,
LAWAPP.QUARTWAGE.PAYROLL_YEAR, LAWAPP.QUARTWAGE.PAY_SUM_GRP, LAWAPP.PAYSUMGRP.DESCRIPTION


GO

**

However, here's the problem. The PIVOT piece of this is not working:

CREATE VIEW vwPAYROLL2
AS
SELECT EMPLOYEE, DESCRIPTION, TOT_WAGES
FROM vwPAYROLL1
PIVOT (
SUM(TOT_WAGES)
FOR EMPLOYEE
) AS payrollpivot
ORDER BY
EMPLOYEE;

**

error msg: Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

SQL Query Analyzer doesn't like the statement:

) AS payrollpivot

***
Any ideas? Thank you.

 

[mrdenny]

They query that you posted doesn't appear to be a PIVOT query. Won't this give you the data that you are looking for?

SELECT EMPLOYEE, DESCRIPTION, SUM(TOT_WAGES)
FROM vwPAYROLL1
ORDER BY 
    EMPLOYEE;

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[psimon1]

No, that didn't work:

Msg 8120, Level 16, State 1, Line 1
Column 'vwPAYROLL1.EMPLOYEE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[mrdenny]

Whops, my bad.

SELECT EMPLOYEE, DESCRIPTION, SUM(TOT_WAGES)
FROM vwPAYROLL1
GROUP BY EMPLOYEE, DESCRIPTION
ORDER BY EMPLOYEE;

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dhulbert]

Then

SELECT EMPLOYEE, DESCRIPTION, SUM(TOT_WAGES)
FROM vwPAYROLL1
GROUP BY EMPLOYEE, DESCRIPTION
ORDER BY EMPLOYE

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[psimon1]

I tried this but really didn't have to:

SELECT EMPLOYEE, DESCRIPTION, SUM(TOT_WAGES)
FROM vwPAYROLL1
GROUP BY EMPLOYEE, DESCRIPTION
ORDER BY EMPLOYEE

is a basic select statement that gives me transaction-level data; I'm really looking for this to be summarized, akin to either a cross-tab query in Access or a pivot table in Excel.

I've talked to a few people who've used SQL server 2005 but no one has any experience with the PIVOT command.

Of course, I can always do a Crystal Crosstab report.

 

[mrdenny]

Based on the columns you have told us about there isn't anything to cross tab.

Based on the initial query that you provided you are simply trying to get every employee and there total pay. The simple select statement provided will do this.

Now if you wanted to view salary by year using the data from the view you created above that would be something that you can pivot.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[psimon1]

If you go to the original query, you'll see what I'm trying to PIVOT.

CREATE VIEW vwPAYROLL2
AS
SELECT EMPLOYEE, DESCRIPTION, TOT_WAGES
FROM vwPAYROLL1
PIVOT (
SUM(TOT_WAGES)
FOR EMPLOYEE
) AS payrollpivot
ORDER BY
EMPLOYEE;

 

[mrdenny]

But that's not a pivot. That's a basic sum.

This is a Pivot. We are taking a single base column TOT_WAGES and breaking it into two columns based on a second base column. Your posted code takes a single base column and turns it into a single column. This isn't a pivot table.

CREATE VIEW vwPAYROLL2
AS 
SELECT EMPLOYEE, DESCRIPTION, 2004_TOT_WAGES, 2005_TOT_WAGES
FROM (SELECT EMPLOYEE, DESCRIPTION, PAYROLL_YEAR, TOT_WAGES
FROM vw_PAYROLL1) p
PIVOT (
    SUM(TOT_WAGES)
    FOR PAYROLL_YEAR IN ([2004], [2005])
) AS payrollpivot
ORDER BY 
    EMPLOYEE;

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[psimon1]

Thank you for the response, but this still doesn't work. I only have 2007 data in the system and don't have a need to break it out by year. I can derive payroll month or even use the check date, but I really need to put the 'buckets' of pay into different columns.

 

[ESquared]

psimon1,

It should be clear that no one here can figure out what you're trying to do. The query you've posted twice doesn't have any pivoting in it.

So would you give us a SAMPLE of what the output should look like? Put column names and two rows of made-up example data.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[psimon1]

Sure thing.

TABLE

EMPLOYEE, TYPE OF PAY, AMOUNT, DATE
123, REG, 10, 4/1/06
123, OVT, 20, 4/1/06
111, REG, 10, 4/1/06
111, REG, 10, 4/8/06
111, REG, 10, 4/7/06

RESULTING IN THE FOLLOWING PIVOT

EMPLOYEE TYPE OF PAY

REG OVT
123 10 20
111 30 0

Is this clearer now?

 

[ESquared]

This is the first time you've mentioned pay type, the column you want to pivot on.

CREATE TABLE #EmpPay (
   EmpID int NOT NULL,
   PayType char(3) NOT NULL CONSTRAINT CK_#EmpPay_PayType_IsValid CHECK (PayType IN ('REG', 'OVT')),
   PayDate datetime NOT NULL
      CONSTRAINT DF_#EmpPay_PayDate DEFAULT (DateAdd(dd, DateDiff(dd, 0, GetDate()), 0))
      CONSTRAINT CK_#EmpPay_PayDate_HasNoTime CHECK (PayDate = DateAdd(dd, DateDiff(dd, 0, PayDate), 0)),
   Amount numeric(20,2) NOT NULL,
   CONSTRAINT PK_#EmpPay PRIMARY KEY CLUSTERED (EmpID, PayType, PayDate)
)

INSERT #EmpPay SELECT 123, 'REG', '4/1/06', 10
INSERT #EmpPay SELECT 123, 'OVT', '4/1/06', 20
INSERT #EmpPay SELECT 111, 'REG', '4/1/06', 10
INSERT #EmpPay SELECT 111, 'REG', '4/8/06', 10
INSERT #EmpPay SELECT 111, 'REG', '4/7/06', 10

SELECT EmpID, REG = IsNull(REG, 0), OVT = IsNull(OVT, 0)
FROM
  #EmpPay
PIVOT (
   Sum(Amount)
   FOR PayType IN (REG, OVT)
) PayPivot

DROP TABLE #EmpPay

Result set:
[tt]EmpID REG OVT
111 10.00 0.00
123 10.00 20.00
111 10.00 0.00
111 10.00 0.00[/tt]

This is the first time I have ever done a PIVOT query in SQL 2005. I read SQL Server 2005 Books Online: Using PIVOT and UNPIVOT to learn how to do it. I took one of the pivot examples and replaced columns until I had a working query.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

oh, I forgot to do an aggregate, let me fix it... just a minute.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

SELECT EmpID, REG = [red]Sum([/red]IsNull(REG, 0)[red])[/red], OVT = [red]Sum([/red]IsNull(OVT, 0)[red])[/red]
FROM
   #EmpPay
   PIVOT (
      Sum(Amount)
      FOR PayType IN (REG, OVT)
   ) PayPivot
[red]GROUP BY EmpID[/red]

Result set:
[tt]
EmpID REG OVT
111 30.00 0.00
123 10.00 20.00[/tt]

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[psimon1]

Thanks! I was able to get this now.

I was hoping that the Pivot function wouldn't require me to enter each type of pay because there are almost 200 types of pay. This will work though.

Thank you!

 

[ESquared]

Unfortunately the pivot does work this way.

You know, pivoting is often a presentation issue. Have you considered doing this in the client instead of the server? It often belongs there.

Excel can pivot easily, for example...
So can Access...
So can Crystal Reports...
And probably other products. None of them requiring you to list the types of pay.

If you are really determined to do it on the server and you want to be tolerant of the changes to the types of pay, create a stored procedure which alters/creates the pivot query for you based on the data in your pay types table. Run the stored procedure from a trigger on the pay types table so any updates are always immediately reflected in the query/view/sp that you automatically build.

This kind of preprocessing gives you the best of both worlds: performance combined with data-driven behavior, without using dynamic SQL (except to create the objects themselves).

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

I studied PIVOT to make sure I understood it and found that I'd made a mistake (of sorts, although I did get the right resultset). Pivot performs an implicit GROUP BY on all columns of the pivoting table/query not in the pivoted data.

So using the original non "GROUP BY" query above:

SELECT EmpID, REG = Sum(IsNull(REG, 0)), OVT = Sum(IsNull(OVT, 0))
FROM
   [red]#EmpPay[/red]
   PIVOT (
      Sum(Amount)
      FOR PayType IN (REG, OVT)
   ) PayPivot
GROUP BY EmpID

Since #EmpPay has another column in it, PayDate, the resultset is grouped by it and there is more than one row for each EmpID. To fix it, switch out the table name for a derived table query that brings only the columns desired into the equation:

SELECT EmpID, REG, OVT
FROM
   [red](SELECT EmpID, PayType, Amount FROM #EmpPay) x[/red]
   PIVOT (
      Sum(Amount)
      FOR PayType IN (REG, OVT)
   ) PayPivot

NOW you have the correct way to pivot. If you need other columns from the #EmpID table, you'll need to join to them somehow or include them in the derived table with your own custom GROUP BYs in there.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

By the way, see Auto Pivot in SQL 2005, an SP I wrote to cement my new understanding of the PIVOT command.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]