Selecting Fields as Columns - 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)

Selecting Fields as Columns

Author

Topic

tomrippity
Starting Member

37 Posts

Posted - 2012-08-15 : 11:33:23

I am trying to select from a table where field names show up as columns, so that instead of getting 5 rows of data, each with 1 data type seperating them, I get 1 row with an additional 5 columns. I have the current result and desired result as an excel file linked below.

Thank you in advance!

http://dl.dropbox.com/u/55890623/result_set.xls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 11:49:58

[code]
SELECT DISTRICT_STUDENT_ID,
STATE_STATE_ID,
FIRST_NAME,
LAST_NAME,
MAX(CASE WHEN SHORT_DESCRIPTION = 'WEIGHT' THEN SCORE END) AS WEIGHT,
MAX(CASE WHEN SHORT_DESCRIPTION = 'HEIGHT' THEN SCORE END) AS HEIGHT,
MAX(CASE WHEN SHORT_DESCRIPTION = 'CURL_UPS' THEN SCORE END) AS CURL_UPS,
MAX(CASE WHEN SHORT_DESCRIPTION = 'SIT_REACH' THEN SCORE END) AS SIT_REACH,
MAX(CASE WHEN SHORT_DESCRIPTION = 'MILE' THEN SCORE END) AS MILE,
DATE_ENTERED
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY DISTRICT_STUDENT_ID,
STATE_STATE_ID,
FIRST_NAME,
LAST_NAME
ORDER BYDATE_ENTERED DESC) AS Rn,*
FROM table
)t
WHERE Rn=1
GROUP BY DISTRICT_STUDENT_ID,
STATE_STATE_ID,
FIRST_NAME,
LAST_NAME,
DATE_ENTERED
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tomrippity
Starting Member

37 Posts

Posted - 2012-08-15 : 12:04:50

Thank you Visakh16!

I am also curious, is there anyway to automate adding columns?

For example, a school might add new exercises, which would then require me to go in and modify this query to reflect the new columns. Is there someway I can fix it so if they add an exercise "push-ups" lets say, that it automatically adds the columns "PUSH_UPS"?

Essentially replace this:

MAX(CASE WHEN SHORT_DESCRIPTION = 'WEIGHT' THEN SCORE END) AS WEIGHT,
MAX(CASE WHEN SHORT_DESCRIPTION = 'HEIGHT' THEN SCORE END) AS HEIGHT,
MAX(CASE WHEN SHORT_DESCRIPTION = 'CURL_UPS' THEN SCORE END) AS CURL_UPS,
MAX(CASE WHEN SHORT_DESCRIPTION = 'SIT_REACH' THEN SCORE END) AS SIT_REACH,
MAX(CASE WHEN SHORT_DESCRIPTION = 'MILE' THEN SCORE END) AS MILE

with some query to build the columns

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 12:29:14

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources