merge two columns-Need help Immediately

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

merge two columns-Need help Immediately

Author

Topic

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-11-12 : 04:46:46

hi all,

i have 2 columns incidenttype and othertype
in my data any one of the column among these two will be null for each record.
i.e.,

recordid IncidentType Othertype
1 Null Eve Teasing
2 Robbery Null
3 Illegal Selling Null

I want data to be populated as recordid and Type where type should populate the value that is not null

i.e., I want the result to be displayed in this format:

recordid Type
1 Eve Teasing
2 Robbery
3 Illegal Selling

IS this possible, if so plaselet me know how to write a query.
If not possible,please suggest me how I can solve this.

Thanks for your help. Your help will be very much appreciated

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-12 : 04:52:53

select recordid, isnull(incidenttype,othertype) as Type
from table.

Read BOL to learn how to handle NULL values.

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-11-12 : 05:07:19

Thanks for your qiuck reply. but just now came toknow that instead of null in Incident type field it stores a string "other"

i,e.,

recordid IncidentType Othertype
1 other Eve Teasing
2 Robbery Null
3 Illegal Selling Null

how does the same scenario works if i want to merge the second column value whenevr the data in first column is "other" .
I apolozise for not checking before posting for the first time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 05:13:24

[code]SELECT recordID,
COALESCE(NULLIF(incidentType, 'Other'), otherType)
FROM Table1[/code]

E 12°55'05.63"
N 56°04'39.26"

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-11-12 : 05:20:00

works great. Thanks a ton!!

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-11-12 : 10:13:37

One more question realted to this. How can i get the above query in which the where condition should check for a phrase in any of the othertype and Incidenttype and retrive teh result as the above query.

i.e ., with respect to about data if i want to search for 'Eve' or for a letter 's', please let me know how this hsould be handled.

Your help will be very much appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 10:18:17

Please provide proper and accurate sample data and your expected output based on the provided sample data.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:21:25

quote:
Originally posted by RaghaSM

One more question realted to this. How can i get the above query in which the where condition should check for a phrase in any of the othertype and Incidenttype and retrive teh result as the above query.

i.e ., with respect to about data if i want to search for 'Eve' or for a letter 's', please let me know how this hsould be handled.

Your help will be very much appreciated

is this what you want?

SELECT *
FROM
(
SELECT	recordID,
	COALESCE(NULLIF(incidentType, 'Other'), otherType) AS Type
FROM	Table1
)t
WHERE t.Type LIKE '%Eve%'
OR t.Type LIKE '%s%'

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-11-12 : 10:30:41

Here is my sample data:

ID IncidentType Othertype
6 Alcohol violation NULL
8 Select Eve Teasing
9 Select Consumption of Alchohol while driving

and I want to retrive the data as you specifeid earlier :

SELECT recordID,
COALESCE(NULLIF(incidentType, 'Other'), otherType) as type
FROM Table1

with an additional feature to check for the type containing 'Alcohol' word from the query retrived.
i.,e the result should be

ID Type
6 Alcohol violation
9 Consumption of Alchohol while driving

I hope this is clear. Please let me know if its still not clear

Thanks for your help.

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-11-12 : 10:36:27

quote:
Originally posted by visakh16

quote:
Originally posted by RaghaSM

One more question realted to this. How can i get the above query in which the where condition should check for a phrase in any of the othertype and Incidenttype and retrive teh result as the above query.

i.e ., with respect to about data if i want to search for 'Eve' or for a letter 's', please let me know how this hsould be handled.

Your help will be very much appreciated

is this what you want?
SELECT *
FROM
(
SELECT recordID,
COALESCE(NULLIF(incidentType, 'Other'), otherType) AS Type
FROM Table1
)t
WHERE t.Type LIKE '%Eve%'
OR t.Type LIKE '%s%'

Thanks a lot. This helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:38:56

quote:
Originally posted by RaghaSM

quote:
Originally posted by visakh16

is this what you want?
SELECT *
FROM
(
SELECT recordID,
COALESCE(NULLIF(incidentType, 'Other'), otherType) AS Type
FROM Table1
)t
WHERE t.Type LIKE '%Eve%'
OR t.Type LIKE '%s%'

Thanks a lot. This helps.

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources