Create column from other columns in same table

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

Create column from other columns in same table

Author

Topic

Kevin C
Starting Member

3 Posts

Posted - 2011-04-12 : 16:36:01

Hello,

I have two sets of Lat/Long coordinates. When [DATASOURCE] is let's say X I want values from ,
[LATCOD08] --NCES LAT
[LONCOD08] --NCES LONG to go to a new column and when [DATASOURCE] is let's say Y I want ,
geometry::XXXXXX).STX AS D_LONG
geometry::XXXXXX).STY AS D_LAT
to go in the same new column.

Thanks for your answers, I apologize I am new to this. I hope I made myself clear.

i.e.
[ROWID]
,[LATCOD08] --NCES LAT
,[LONCOD08] --NCES LONG
,[DATASOURCE]
,geometry::XXXXXX).STX AS D_LONG
,geometry::XXXXXX).STY AS D_LAT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-12 : 17:55:34

Maybe it's just me, but your post is not clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kevin C
Starting Member

3 Posts

Posted - 2011-04-12 : 18:07:16

Ok, let me try to explain what I am trying to do
I have 5 colums:

lat_1 | long_1 | lat_2 | long_2 | Source
__________________________________________
32 \\\\ -117 \\\\ 33 \\\\ -117 \\\\ Parcel
32 \\\\ -118 \\\\ 33 \\\\ -117 \\\\ Street_line

When source is parcel, I want to take lat_1 and lat_2 and put the records in 2 new columns. When source is Parcel I want to do the same for lat_2 and long_2.

It should look like that:

lat_1 | long_1 | lat_2 | long_2 | Source | lat_3 | long_3
____________________________________________________________________
32 \\\\ -117 \\\\ 33 \\\\ -117 \\\\ \\\Parcel 32 \\\\ -117
32 \\\\ -118 \\\\ 33 \\\\ -117 Street_line \\\\ 33 \\\\ -117

I am not asking for a detailled answer, just a few clues so I know where to start.

Thanks

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-04-12 : 18:46:52

This should give you an idea, if I understood your question correctly:

declare @t table (lat_1 int, long_1 int, lat_2 int, long_2 int, Source varchar(20))
insert @t (lat_1, long_1, lat_2, long_2, Source)
select 32, -117, 33, -117, 'Parcel' union
select 32, -118, 33, -117, 'Street_line'

select lat_3 = case Source when 'Parcel' then lat_1 when 'Street_line' then lat_2 end,
long_3 = case Source when 'Parcel' then long_1 when 'Street_line' then long_2 end
from @t

Some days you're the dog, and some days you're the fire hydrant.

Kevin C
Starting Member

3 Posts

Posted - 2011-04-12 : 19:48:50

I figured it out i think:

SELECT

[ROWID]
,[xxxxx_DATASOURCE]
,geometry::STGeomFromText([xxxxxx).STX AS LONG
,geometry::STGeomFromText([xxxxx).STY AS LAT

FROM[TestData].[dbo].[xxxxSchools]
WHERE [TestData].[dbo].[xxxxSchools].[xxxx_DATASOURCE] = 'Parcels'

SELECT

[ROWID]
,[xxxxDATASOURCE]
,[LONCOD08] --xxx LONG
,[LATCOD08] --xxx LAT

FROM[xxxx]
WHERE [xxxxx].[xxxxDATASOURCE] <> 'Parcels'

ORDER BY ROWID ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources