How to flatten these results? - 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 2005 Forums

Transact-SQL (2005)

How to flatten these results?

Author

Topic

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2007-03-23 : 15:12:03

Hi folks,

I got a table structured like this:

ResponseID (int)
ItemText (varchar(255))
AnswerValue (varchar(255))

Example rows look like this:
ResponseID ItemText AnswerValue
1 Gender Male
1 Ethnicity White
1 Smokes Yes
2 Ethnicity Spanish
2 Ethnicity White
2 Gender Female
2 Gender Male
3 Smokes Yes
4 Gender Female

Basically I need to return all combinations of answers given to an ItemText by ResponseID (one row per response). So it would look like this ...

ResponseID Gender Ethnicity Smokes
1 Male White Yes
2 Female Spanish NULL
2 Female White NULL
3 NULL NULL Yes
4 Female NULL NULL

In this example, a response can be associated to multiple Ethnicity values.

Any ideas on the most direct, efficient way to do this in sql?

thanks - wg

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-23 : 15:49:20

You sure the sample data is corect, I mean how can response ID be both male and female?

Oh, wait...I think I've heard of that


CREATE TABLE myTable99(
	  ResponseID  int
	, ItemText    varchar(255)
	, AnswerValue varchar(255))
GO

INSERT INTO myTable99(ResponseID, ItemText, AnswerValue)
SELECT 1, 'Gender',    'Male'	 UNION ALL
SELECT 1, 'Ethnicity', 'White'	 UNION ALL
SELECT 1, 'Smokes',    'Yes'	 UNION ALL
SELECT 2, 'Ethnicity', 'Spanish' UNION ALL
SELECT 2, 'Ethnicity', 'White'	 UNION ALL
SELECT 2, 'Gender',    'Female'	 UNION ALL
SELECT 2, 'Gender',    'Male'	 UNION ALL
SELECT 3, 'Smokes',    'Yes'	 UNION ALL
SELECT 4, 'Gender',    'Female'
GO

   SELECT a.ResponseID, Gender, Ethnicity, Smokes
     FROM (SELECT DISTINCT  ResponseID FROM myTable99) AS a
LEFT JOIN (SELECT ResponseID, AnswerValue AS Gender    
	     FROM myTable99 WHERE ItemText = 'Gender'   ) AS b
       ON b.ResponseID = a.ResponseID
LEFT JOIN (SELECT ResponseID, AnswerValue AS Ethnicity 
	     FROM myTable99 WHERE ItemText = 'Ethnicity') AS c
       ON c.ResponseID = a.ResponseID
LEFT JOIN (SELECT ResponseID, AnswerValue AS Smokes    
	     FROM myTable99 WHERE ItemText = 'Smokes'   ) AS d
       ON d.ResponseID = a.ResponseID

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2007-03-24 : 02:12:31

thanks Brett!

worked perfectly ... and btw, the multiple male and female values per response is just the tip of the iceberg.

- wg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources