Pivot or Other Way

I have a view of a SQL table that performs a complicated calculation that is called [Overall Rating].  The view has:  faciliy number, fiscal year, and the [overall rating].  

Facility Number                  FY                   Overall Rating
1                                           2006               Poor
2                                           2006              Average
3                                           2007              Superior
1                                           2009              Adequate
2                                           2010              Average

I want to pivot the data to list the facility number for each row and then columns for 2006, 2007, 2008, 2009, 2010, etc, with the rating for that fiscal year appearing in that column
so that it looks like:
Facility Number                     2006             2007     2008     2009              2010
1                                             Poor                                        Adequate
2                                             Average                                                        Average
3                                                                 Superior

Can I do that or do I have to attack this from a different direction?

Thanks in advance for your help....
mounty95Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OMC2000Commented:
try this
DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)

SET @Columns = substring((select ',['+FY+']' from t1 group by FY for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM 
  (Select FY , Facility, Rating from t1) sourcedata
PIVOT
  (max(Rating) for FY in ('+@Columns+')) pivottable'

EXEC(@sql)
GO

Open in new window


theory from Mark Wills is here:

https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
0
mounty95Author Commented:
In trying to run this, I get a Line 4 Error converting data type varchar to numeric.

The FY field in the view is numeric so I am not sure what it thinks it is varchar.
0
OMC2000Commented:
Then it should be like this:

DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)

SET @Columns = substring((select ',['+convert(varchar(4), FY)+']' from t1 group by FY for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM 
  (Select convert(varchar(4), FY) as FY , Facility, Rating from t1) sourcedata
PIVOT
  (max(Rating) for FY in ('+@Columns+')) pivottable'

EXEC(@sql)
GO

convert(varchar(4), FY)

Open in new window

0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

mounty95Author Commented:
Msg 8115, Level 16, State 5, Line 4
Arithmetic overflow error converting numeric to data type varchar.


What am I missing in providing relevant information on this?

Thank you OMC2000 for your patience with me on this....
0
OMC2000Commented:
It must happen at convert(varchar(4), FY), error message means that numeric value is longer than 4 character.

You could find them using
select FY from t1 where FY > 9999 or FY < 0

If you really need these longer values, replace varchar(4) with varchar(10) in both places
0
mounty95Author Commented:
We are getting somewhere!!!!!  Now my question is this:  the columns are not in sequential order.  Is there a way to specify this?  By not "in sequential order" I mean that the columns right now are ordered:  2008, 2007, 2017, 2016, 2012, 2014 etc.

I found the one record that throws off the FY field.  Some brilliant soul had an extra "0" in the FY field and that created the issue on the varchar (4) problem.
0
OMC2000Commented:
try this
DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)

SET @Columns = substring((select ',['+convert(varchar(4), FY)+']' from t1 group by FY for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM 
  (Select convert(varchar(4), FY) as FY , Facility, Rating from t1) sourcedata
PIVOT
  (max(Rating) for FY in ('+@Columns+')) pivottable ORDER BY 1'

EXEC(@sql)
GO

Open in new window


or this

DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)

SET @Columns = substring((select ',['+convert(varchar(4), FY)+']' from t1 group by FY for xml path('')),2,8000)
SET @SQL = 'select * from (SELECT * FROM 
  (Select convert(varchar(4), FY) as FY , Facility, Rating from t1) sourcedata
PIVOT
  (max(Rating) for FY in ('+@Columns+')) pivottable) s1 ORDER BY 1'

EXEC(@sql)
GO

Open in new window

0
mounty95Author Commented:
Both examples returned similar results with the column out of sequence.

I was able to hard code the years by changing the select to specify the FY like this:

"SELECT Facility, [2007],[2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018] FROM "

Just means that I have to change the SQL each year to include the next year's data...
0
OMC2000Commented:
sorry, I misunderstood you
try this

DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)

SET @Columns = substring((select ',['+convert(varchar(4), FY)+']' from t1 group by FY order by FY for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM 
  (Select convert(varchar(4), FY) as FY , Facility, Rating from t1) sourcedata
PIVOT
  (max(Rating) for FY in ('+@Columns+')) pivottable'

EXEC(@sql)
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mounty95Author Commented:
Thank you so much for your help on this!!!!!  It works perfectly!!!!!!
0
mounty95Author Commented:
OMCC,
Is there any reason why I would not be able to create this as a view?  I get the most ambiguous of errors when saving this code as a view:  "Incorrect syntax near the word DECLARE."  I am not sure what is incorrect.  Is it not allowed to create variables as part of the view?  How would I get around this.  I want to make this view available for staff to connect to and the code is perfect, just can't get the view to save.
0
OMC2000Commented:
mounty95, you cannot create view using multistatement SQL script.
If you need it, you should create a stored procedure, based on these statements and then either select data from it, or create view based on such procedure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.