How to query rows and display as columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to query rows and display as columns?

Author

Topic

Jon G
Starting Member

31 Posts

Posted - 2011-04-10 : 09:32:34

Hi,

I have an SQL 2005 database that has information stored in rows that I need to query and show the results in the following format. Can anyone help me with a full sql statement to do this as it is driving me nuts?

thanks in advance
Jon

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-10 : 10:03:41

SELECT lessontime, lessonday, [Dave Ellis] Dave_Ellis, [Tony White] Tony_White, [Donna Summer] Donna_Summer
FROM (SELECT * FROM TEACHDIARY) a
PIVOT(MAX(Student) FOR Tutor IN ([Dave Ellis],[Tony White],[Donna Summer])) b

You can find more information about PIVOT in Books Online.

shangbaby
Starting Member

3 Posts

Posted - 2011-04-10 : 10:44:44

quote:
Originally posted by Jon G

Hi,

I have an SQL 2005 database that has information stored in rows that I need to query and show the results in the following format. Can anyone help me with a full sql statement to do this as it is driving me nuts?

thanks in advance
Jon

Sorry for your frustration, and I don't mean to add to it with a lengthy explanation (I have been where you are) but after reviewing your issue it sounds like you need a SQL SERVER 2005 PIVOT STATEMENT...which does the following:

* It allows you to transform rows of data into analytical columns
* Allows for many applications for the result sets
There are three components that you must keep in mind when creating a SS PIVOT statement:
* The PIVOT STATEMENT itself
* an aggregate function
* The values in a column belonging to a specific set of possible values (this will represent the new column structure )
* It must be a static list. Therefore, if the list is variable you must use Dynamic SQL.

You have a couple of issues here. I may be unaware of some missing pieces but feel free to fill in after this.

Your data isn't normalized. (I am unaware of your level of understanding with RDBMS concepts so I apologize if I sound like I am explaining either over or under your head :) ) I know you want a quick answer in the depths of your frustration, but the results you want can only come with advanced T-SQL or the SQL 2K solution of using in-line CASE Statement with a GROUP BY. I am unaware if you know this aspect of ETL or not.

You need four tables one that holds the course information, one that holds instructor information,one that holds student information, and one that holds enrollment information. A T-SQL PIVOT operator needs the aggregate function as well as a static list as I mentioned above. In other words It needs an aggregate function, an aggregate element and a 'spreading' element. Also, do you know that this list of Tutors and Students will not change? If they are changing then you will have to use Dynamic SQL.

I can help you for free, but I would have to create what you need. Let me know your timetable..

Shangz R Brown
SQL DATABASE DEVELOPER
SSRS, SSIS, T-SQL, BIDS

Jon G
Starting Member

31 Posts

Posted - 2011-04-10 : 10:53:27

Thanks for your help guys.

robvolk your solution worked perfectly...just what I need.

many thanks

Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources