Rows to Columns for selected rows

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)

Rows to Columns for selected rows

Author

Topic

BarryKeithNoble
Starting Member

9 Posts

Posted - 2007-08-15 : 07:12:29

Hello

My external database administrators have done as I have requested and added two new field types relating to postcodes, which are district and ward (There being many postcodes in a ward and many wards in a district). However they have added both district and ward in the same field with another field in a table identifying which is which.

This gives me an output as such
Postcode AreaName ListDefinedAreaTypeID
AL1 1SD E01023740 6
AL1 1SD SA7 7
AL1 1SE E01023740 6
AL1 1SE SA7 7
AL1 1SF E01023741 6

from the following code

SELECT
DefinedAreaAddress.Postcode,
DefinedArea.AreaName,
DefinedArea.ListDefinedAreaTypeID
FROM
DefinedArea
LEFT OUTER JOIN DefinedAreaAddress ON
DefinedAreaAddress.DefinedAreaID = DefinedArea.DefinedAreaID

Whereas the output I would want to see (If ward & District where split into different fields) would be something like this

Postcode Ward District
AL1 1SD E01023740 SA7
AL1 1SE E01023740 SA7
AL1 1SF E01023741 SA7

I'm not sure if this is a strange request or whether my Database administrators know something I don't. But any help or ideas in switching the output form would be gratefully recieved.

Regards
BarryN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 08:38:25

[code]SELECT b.Postcode,
Ward = MAX(CASE WHEN a.ListDefinedAreaTypeID = 6 THEN a.AreaName END),
District = MAX(CASE WHEN a.ListDefinedAreaTypeID = 7 THEN a.AreaName END)
FROM DefinedArea a
LEFT OUTER JOIN DefinedAreaAddress b
ON b.DefinedAreaID = a.DefinedAreaID
GROUP BY b.Postcode[/code]

KH
[spoiler]Time is always against us[/spoiler]

BarryKeithNoble
Starting Member

9 Posts

Posted - 2007-08-21 : 07:35:32

Thanks Khtan

This code seems to work straight off. I can now adapt it for further use.

BarryN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources