Column Heading By Year - 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)

Column Heading By Year

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-04-07 : 18:31:53

I have 4 tables as follows:

declare @Employee table (
ID int,
Name varchar(20)
)

insert into @Employee (
ID,
Name
)
values
(1, 'Dave'),
(2, 'Hal'),
(3, 'Bill')

declare @Review table (
ID int,
EmployeeID int,
ClosedDate date
)

insert into @Review (
ID,
EmployeeID,
ClosedDate
)
values
(1, 1, '2008-05-10' ),
(2, 1, '2009-06-01' ),
(3, 1, '2010-04-25' ),
(4, 2, '2008-04-09' ),
(5, 2, '2009-05-11' ),
(6, 2, '2010-06-02' )

declare @Metric table (
ID int,
MetricDesc varchar(100)
)

insert into @Metric (
ID,
MetricDesc
)
values
(1, 'Metric One' ),
(2, 'Metric Two' ),
(3, 'Metric Three' )

declare @ReviewRating table (
ReviewID int,
MetricID int,
Rating int
)

insert into @ReviewRating(
ReviewID,
MetricID,
Rating
)
values
(1, 1, 3),
(1, 2, 4),
(1, 3, 4),
(2, 1, 4),
(2, 2, 4),
(2, 3, 4),
(3, 1, 5),
(3, 2, 2),
(3, 3, 2),
(4, 1, 2),
(4, 2, 3),
(4, 3, 2),
(5, 1, 3),
(5, 2, 4),
(5, 3, 4),
(6, 1, 5),
(6, 2, 4),
(6, 3, 5)

SELECT
R.ClosedDate,
E.Name,
M.MetricDesc,
RR.Rating
FROM
@Employee E,
@Metric M,
@Review R,
@ReviewRating RR
WHERE
E.ID = R.EmployeeID AND
M.ID = RR.MetricID AND
R.ID = RR.ReviewID
ORDER BY
Name

I need a query that returns the results like this:

 
Name	Metric Desc	2008	2009	2010
Dave	Metric One	3	4	5
Dave	Metric Two	4	4	2
Dave	Metric Three	4	4	2
Hal	Metric One	2	3	5
Hal	Metric Two	3	4	4
Hal	Metric Three	2	4	5

But don't know how to get the years as the column headings and then the ratings for the metric for the year. Also when new years are added how will that be added in?

Thanks in advance.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-07 : 19:00:31

look at this http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx


EXEC dynamic_pivot 'SELECT E.Name, M.MetricDesc, RR.Rating FROM
Employee E, Metric M, Review R, ReviewRating RR WHERE
E.ID = R.EmployeeID AND M.ID = RR.MetricID AND R.ID = RR.ReviewID ',
'YEAR(ClosedDate)',
'SUM(Rating)'

If you don't have the passion to help people, you have no passion

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-04-08 : 11:23:55

Thanks!
Works perfectly!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 12:02:46

thank madhivan!

If you don't have the passion to help people, you have no passion

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-04-08 : 14:29:31

So if I want to use this in a stored proc and am passing a parameter how do I enclose the paramter?

For example, I am passing an int as employeeid.


CREATE PROCEDURE [dbo][PROC_NAME]
@pEmployeeId int, --ie 46
AS
BEGIN
EXEC dynamic_pivot 'SELECT E.Name, M.MetricDesc, RR.Rating FROM
Employee E, Metric M, Review R, ReviewRating RR WHERE
E.ID = '+@pEmployeeId+' AND
E.ID = R.EmployeeID AND M.ID = RR.MetricID AND R.ID = RR.ReviewID ',
'YEAR(ClosedDate)',
'SUM(Rating)'
END

I have tried different single quote combos, but either get syntax errors or
Conversion failed when converting the varchar value '+@pEmployeeId+' to data type int.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 19:05:22

you will need to convert that baby to nvarchar(x) as such


CREATE PROCEDURE [dbo][PROC_NAME]
@pEmployeeId int, --ie 46
AS
BEGIN
EXEC dynamic_pivot 'SELECT E.Name, M.MetricDesc, RR.Rating FROM
Employee E, Metric M, Review R, ReviewRating RR WHERE
E.ID = '+ CAST(@pEmployeeId AS nvarchar(25))   +' AND
E.ID = R.EmployeeID AND M.ID = RR.MetricID AND R.ID = RR.ReviewID ',
'YEAR(ClosedDate)',
'SUM(Rating)'
END

If you don't have the passion to help people, you have no passion

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-11 : 22:12:27

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. Unless you have only one employee, that table should be named Personnel. It is a set of things. That is how you name it. I also see that you have a magical “id” that can be anything in the entire universe – a person, a metric, a squid or whatever.
You have split personnel review into too many tables. If you normalize the schema, life is so much easier

CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL);

CREATE TABLE Metrics
(metric_code INTEGER NOT NULL PRIMARY KEY,
metric_desc VARCHAR(100)NOT NULL);

CREATE TABLE Employee_Reviews
(emp_id INTEGER NOT NULL
REFERENCES Personnel (emp_id),
close_date DATE NOT NULL,
metric_code INTEGER NOT NULL
REFERENCES Metrics(metric_code),
review_rating INTEGER NOT NULL
PRIMARY KEY (emp_id, close_date, metric_code));

SELECT P.emp_name, M.metric_desc,
MAX(CASE WHEN R.close_date BETWEEN '2008-01-01' AND '2008-12-31'
THEN review_rating ELSE NULL END) AS rating_2008,
MAX(CASE WHEN R.close_date BETWEEN '2009-01-01' AND '2009-12-31'
THEN review_rating ELSE NULL END) AS rating_2009,
MAX(CASE WHEN R.close_date BETWEEN '2010-01-01' AND '2010-12-31'
THEN review_rating ELSE NULL END) AS rating_2010
FROM Employee_Reviews AS R, Metrics AS M. Perosnnnel AS P
WHERE R.metric_code = M.metric_code
AND P.emp_id = R.emp_id
GROUP BY P.emp_name, M.metric_desc;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources