Select data and concatenate
-
we have a table that has 2 columns:
<Filed Name> <Value>
and the data look like:
name1, 'david'
name2, 'jeff'
name3, 'mary'
title1, 'manager'
title2, 'CIO'
title3, 'director'
I need to get the output looks like:
david: manager
jeff:CIO
mary:director
since the values in <Filed Name> have a pattern. e.g the last char is numerical and incremetal by 1.
i can get what i want by using temp table. but i am sure there are better ways to do it. Please enlighten me. thanks a lot.
-
Create Table TTT( FieldName varchar(20), Value varchar(20))
insert into TTT (FieldName, Value ) Values ( 'name1', 'david')
insert into TTT (FieldName, Value ) Values ( 'name2', 'jeff')
insert into TTT (FieldName, Value ) Values ( 'name3', 'mary')
insert into TTT (FieldName, Value ) Values ( 'title1', 'manager')
insert into TTT (FieldName, Value ) Values ( 'title2', 'CIO')
insert into TTT (FieldName, Value ) Values ( 'title3', 'director')
select n.Value + ': ' + t.value
from
ttt n
join
ttt t
on n.FieldName like 'name%'
and
t.FieldName Like 'title%'
and
Right(n.FieldName,patindex('[0-9][a-z]%',Reverse(n.FieldName)))
=
Right(t.FieldName,patindex('[0-9][a-z]%',Reverse(t.FieldName)))
hth
* Noel -
Noel, that is so cool. thanks a lot.
-
I am not going to comment about this design but you should consider changing it
On the other end I posted the query for fun! and I am Glad I could help
* Noel -
IF the table is 2 columnar and you had something like Field1 = David Field2 = CIO all you would have needed to do is something like:
SELECT ISNULL(Field1, '') + ': ' + ISNULL(Field2, '')
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply