Is this possible? - 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

General SQL Server Forums

New to SQL Server Programming

Is this possible?

Author

Topic

supdike
Starting Member

4 Posts

Posted - 2011-02-11 : 17:07:43

I have a SQL statement that joins several tables and returns the following:

Person | type | value
----------------------
0001 | TestA | 99
0001 | TestB | 73
0001 | TestC | 87
0002 | TestA | 67
0002 | TestB | 55
0002 | TestC | 93

I need to combine the rows for each person into one with the "type" becoming the column name and the corresponding value in the column:

Person | TestA | TestB | TestC
------------------------------
0001 | 99 | 73 | 87
0002 | 67 | 55 | 93

Easy?, Nightmare?, Don't attempt it? Any suggestions welcomed.
thanks!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 17:21:13

Look into Pivot ...

This may help:http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Poor planning on your part does not constitute an emergency on my part.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-11 : 17:23:51

SELECT Person,
MAX(CASE WHEN type='TestA' THEN value END) TestA,
MAX(CASE WHEN type='TestB' THEN value END) TestB,
MAX(CASE WHEN type='TestC' THEN value END) TestC
FROM myTable
GROUP BY Person

Or:

SELECT Person, TestA, TestB, TestC
FROM (SELECT Person, Type FROM myTable) A
PIVOT (MAX(value) FOR type in(TestA, TestB, TestC)) B

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-12 : 04:01:32

i hope your type values are static

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

supdike
Starting Member

4 Posts

Posted - 2011-02-14 : 10:59:25

I guess I should have been more specific. There are actually multiple groups of A-B-C record for each person. Max only brings back the largest value of all records. I need each group returnd as a row.

The values are constant. It is the way a vendor product used by several of our clients handled adding a "user defined screen". It "appears" in the application as one screen per group of records, but in actuality every field value is stored as a record.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-14 : 11:36:24

quote:
There are actually multiple groups of A-B-C record for each person

Then you'll need to provide another column or expression to group on, otherwise there's no way for this to work.

supdike
Starting Member

4 Posts

Posted - 2011-02-14 : 13:55:01

There was another column to group on. That was the nudge i needed. I got it to work. I used robvolk's first example. Thanks to all of you who took the time to reply. This newbie appreciates it.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2011-02-14 : 22:36:02

So can you post what you did so others can get the benefit of your experience?

--Jeff Moden

supdike
Starting Member

4 Posts

Posted - 2011-02-15 : 09:15:25

Like I posted, I used robvolk's first example. Here is my working test statement, which is limited to one person:

SELECT pudf.PersonUserDefinedScreen_ID,
MAX (puds.Person_ID) [Person_ID],
MAX(CASE WHEN pudf.UserDefinedField_ID=1 THEN value END) [1Mile],
MAX(CASE WHEN pudf.UserDefinedField_ID=3 THEN value END) [SchYear],
MAX(CASE WHEN pudf.UserDefinedField_ID=4 THEN value END) [1MileGrade],
MAX(CASE WHEN pudf.UserDefinedField_ID=5 THEN value END) [50YdDash],
MAX(CASE WHEN pudf.UserDefinedField_ID=6 THEN value END) [50YdDashGrade],
MAX(CASE WHEN pudf.UserDefinedField_ID=7 THEN value END) [ShuttleRun],
MAX(CASE WHEN pudf.UserDefinedField_ID=8 THEN value END) [ShuttleRunGrade],
MAX(CASE WHEN pudf.UserDefinedField_ID=9 THEN value END) [LongJump],
MAX(CASE WHEN pudf.UserDefinedField_ID=10 THEN value END) [Total],
MAX(CASE WHEN pudf.UserDefinedField_ID=11 THEN value END) [Mslendsit],
MAX(CASE WHEN pudf.UserDefinedField_ID=12 THEN value END) [Mslstljump],
MAX(CASE WHEN pudf.UserDefinedField_ID=13 THEN value END) [Agilsldst],
MAX(CASE WHEN pudf.UserDefinedField_ID=14 THEN value END) [Cardmwk],
MAX(CASE WHEN pudf.UserDefinedField_ID=15 THEN value END) [FlexToe],
MAX(CASE WHEN pudf.UserDefinedField_ID=16 THEN value END) [ArmHangGrade],
MAX(CASE WHEN pudf.UserDefinedField_ID=17 THEN value END) [ArmHang],
MAX(CASE WHEN pudf.UserDefinedField_ID=18 THEN value END) [SitUpsGrade],
MAX(CASE WHEN pudf.UserDefinedField_ID=19 THEN value END) [SitUps],
MAX(CASE WHEN pudf.UserDefinedField_ID=20 THEN value END) [LongJumpGrade],
MAX(CASE WHEN pudf.UserDefinedField_ID=28 THEN (CASE WHEN UserDefinedDropDownItem.Text IS NULL THEN pudf.Value ELSE UserDefinedDropDownItem.Text END) END) [Season],
MAX(CASE WHEN pudf.UserDefinedField_ID=35 THEN value END) [Grade]

FROM PersonUserDefinedScreen puds with (nolock)
INNER JOIN PersonUserDefinedField pudf With (NoLock) ON pudf.PersonUserDefinedScreen_ID = puds.ID
INNER JOIN UserDefinedField udf With (NoLock) ON udf.ID = pudf.UserDefinedField_ID
LEFT OUTER JOIN UserDefinedDropDownItem ON pudf.UserDefinedDropdownItem_ID = UserDefinedDropDownItem.ID
WHERE puds.UserDefinedScreen_ID = 1 and puds.Person_ID = 20307
Group By pudf.PersonUserDefinedScreen_ID

Hope it can help someone.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2011-02-15 : 10:26:06

Thanks. I appreciate it.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources