December 1, 2009 at 9:15 am
Dear ALL,
I have a table named Entity with field names (ID, FieldName)
with the data is as follow :
ID FieldName
1 FullName
2 Address
3 City
4 Country
I need to display these values in column wise.
for ex :
FullName Address City Country
Ravi aaaaa aaa aa
Andy bbbbb bbb bb
Apollo ccccc ccc cc
thanks in advance,
Ammar.
December 1, 2009 at 9:30 am
I'm assuming you're talking about a "one-true-lookup-table" solution. Your sample doesn't include any values, just column names, but the usual thing in this case is that you'd have one column for the name and another for the value. Like:
create table #NameValues (
ID int identity primary key,
EntityID int not null,
ColName varchar(100),
ColValue sql_variant);
insert into #NameValues (EntityID, ColName, ColValue)
select 1, 'LastName', 'Smith' union all
select 1, 'FirstName', 'John';
Is that the kind of situation you're looking at? Or am I misunderstanding your question?
If that's what you're designing, my first recommendation is don't do it. It's one of those things that looks good on paper, but works horribly when you actually start using it.
If you're stuck with it, and can't create actual normalized, relational tables for the data, then the way to query it is:
select FirstName.ColValue as FirstName, LastName.ColValue as LastName
from #NameValues FirstName
inner join #NameValues LastName
on FirstName.EntityID = LastName.EntityID
where FirstName.ColName = 'FirstName'
and LastName.ColName = 'LastName';
This kind of table can store entities (objects) based on an ID number that shows which rows go with which other rows, and which rows represent which columns. It's very flexible and allows for very simple object storage. It also means you're going to have to reinvent your own database engine in your code, either through stored procs or through your data access layer.
You'll have to deal with situations like, "What do I do if one of the rows doesn't have an entry for a first name?" That requires creating very complex outer-join constructs, or using a dynamic XML query in between your table and your final query.
It can be made to work, but it's a major effort. And it will never scale nor perform as well as simply creating well-normalized relational tables and querying them in the standard fashion.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 9:31 am
You're missing a ton of information here. Where are the values coming from?
FullName Address City Country
Ravi aaaaa aaa aa
Andy bbbbb bbb bb
Apollo ccccc ccc cc
Sounds like an EAV system. Please provide sample data for the table holding these records and the original table as per the first link in my signature.
December 1, 2009 at 9:39 am
Garadin (12/1/2009)
You're missing a ton of information here. Where are the values coming from?FullName Address City Country
Ravi aaaaa aaa aa
Andy bbbbb bbb bb
Apollo ccccc ccc cc
Sounds like an EAV system. Please provide sample data for the table holding these records and the original table as per the first link in my signature.
i have mentioned above scenario as an example and the real scenario is similar to that. We have to keep the fields in a separate table and users can add/Edit fields as per their requirement. for that I have to keep the table named Fields which consists of the records (FullName, Address, City, Country, etc).
hope you can understand the situation i have mentioned above.
December 1, 2009 at 9:44 am
GSquared (12/1/2009)
I'm assuming you're talking about a "one-true-lookup-table" solution. Your sample doesn't include any values, just column names, but the usual thing in this case is that you'd have one column for the name and another for the value. Like:
create table #NameValues (ID int identity primary key,
EntityID int not null,
ColName varchar(100),
ColValue sql_variant);
insert into #NameValues (EntityID, ColName, ColValue)
select 1, 'LastName', 'Smith' union all
select 1, 'FirstName', 'John';
Is that the kind of situation you're looking at? Or am I misunderstanding your question?
thanks a lot for your reply, you are correct. let me think about the stuffs that you mentioned after this. will revert back soon.
December 3, 2009 at 12:38 am
Any particular reason why you are following such a design?
"Keep Trying"
December 3, 2009 at 5:00 am
Recommend looking up PIVOT in BOL.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply