April 11, 2012 at 3:33 pm
We've got a couple of tables which store things like a person's ethnic background. However, it stores this information as a bit value, that's bit-OR'ed and saved into a smallint column in these tables. (Unfortunately, the values stored in each table is different, but that's my problem.) I've worked on one program which displays data from one of the tables, and for the rest others have worked on them to display the data. For example, if the person identified themselves as having African American, Caucasian and Asian ethnic background, we would have saved 7 in that person's column. I'm making up a new tables (for each of these separate tables with the basic data) to try and help decode this. So the new table would look something like this:
1 African American
2 Caucasian
4 Asian
The problem I'm having is how do I run against the original table, with this new table that will decipher the information? For example, it would see the 7 stored in the original table, and using the previous listing it would return:
African American and Caucasian and Asian
Kindest Regards, Rod Connect with me on LinkedIn.
April 11, 2012 at 3:37 pm
Hi Rod,
I can visualize your question a bit, but can you please post some data and expected result in consumable format ?
Like given in the link below?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
With 744 visits and 466 posts, i expected you would have done that.
April 11, 2012 at 3:50 pm
I'm not visualizing it as well as ColdCoffee, I fear.
Please post sample data and table structure along with desired results so we can better visualize.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2012 at 6:58 am
You should provide ddl and some test data to explain your problem in more consumable way (link at the bottom of my signature will help you).
I'm not sure if that what you are asking for:
;with def
as
(
select 1 as Code, 'African American' As Origin
union select 2, 'Caucasian'
union select 4, 'Asian'
union select 8, 'Marsian'
)
, tbl
as
(
select 1 as id, 'Bob' as Name, 1 As SelectedOrigin
union select 2, 'Peter' , 2
union select 3, 'Mahmed' , 4
union select 4, 'J.Bush' , 8
union select 5, 'Saint Bob' , 3
union select 6, 'Saint Bob Abu', 7
union select 7, 'Bush Ibn Hattab', 15
)
select t.Name
,stuff((select ' and ' + d.Origin
from def d where d.Code = t.SelectedOrigin & d.Code for xml path('')),1,4,'') Origin
from tbl t
order by t.id
April 12, 2012 at 8:09 am
Rod at work (4/12/2012)
The DDL code that Eugene has produced illustrates what I'm talking about.
Actually, after running the code, it looks like it does what you want.
April 12, 2012 at 9:18 am
Lynn, you're absolutely right, Eugene's code works perfectly. In fact, it's amazing. I love it! It's so elegant. I remember, not too long ago, a SQL Server Central daily newsletter that went out and described the STUFF function. I saved it as something I possibly could use, but didn't think any more about it.
Eugene, I have to ask you about so of the code here. I'm not familiar with the XML keyword in SQL, and it looks like you're using XPath expressions, but there's no XML in this table. Why does this work?
Kindest Regards, Rod Connect with me on LinkedIn.
April 12, 2012 at 10:06 am
"FOR XML PATH ('')" T-SQL relatively new feature is used to return the results in XML form.
It does not need XML, it generates it!
Try to run this:
select d.Origin
from (
select 1 as Code, 'African American' As Origin
union select 2, 'Caucasian'
union select 4, 'Asian'
union select 8, 'Marsian'
) d for xml path('Origin')
You will see XML as a result.
FOR XML PATH ('') construct is widely used to concatenate strings as I've done in my example.
Empty string in PATH, makes T-SQL to not add Root XML tags.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply