Cross-Tab Question - 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)

Cross-Tab Question

Author

Topic

chinkygogo@hotmail.com
Starting Member

3 Posts

Posted - 2008-05-29 : 07:35:02

Hi

I am using SQL server 2005 and want to write a cross-tab query. i have managed a basic one but can't seem to write a more complex one.
if i have a table with columns called id, person_id, field_description, and content. e.g. image that the two values i could have in field description are age and favourite_colour, and then the corrosponding content for these fields would be 56yrs and blue or something.

now i want column names as person_id, age and favourite_colour. and the actual data should be content.

Have i made sense? I am sure this must be simple, but can't figure the syntax

thanks

pete

raja_saminathan
Starting Member

12 Posts

Posted - 2008-05-29 : 08:00:19

can you post the Table Schema and sample input datas and expected results

Rajesh

chinkygogo@hotmail.com
Starting Member

3 Posts

Posted - 2008-05-29 : 08:07:52

Schema:
id(int, pk)
person_id(int)
field_description(nvarchar(50))
content(nvarchar(50))

Sample data from Scheme above (comma seperated):
id, person_id, field_description, content
1, 1, age, 56yrs
2, 1, favourite_colour, blue
3, 2, age, 47yrs
4, 2 favourite_colour, green

Expected Result (comma seperated)
person_id, age and favourite_colour
1, 56yrs, blue
2, 47yrs, green

(i have been using the PIVOT query so far to construct the cross-tab)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 08:34:54

[code]SELECT person_id,
MAX(Case WHEN field_Description = 'age' THEN content ELSE NULL END) AS age,
MAX(Case WHEN field_Description = 'favourite_colour' THEN content ELSE NULL END) AS favourite_colour
FROM Table
GROUP BY person_id[/code]

chinkygogo@hotmail.com
Starting Member

3 Posts

Posted - 2008-05-29 : 08:36:45

Brilliant thank you. i was going to try and use the PIVOT query but i think i will revert to the original way of doing cross-tabs as this is supported on non-2005 servers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources