Dynamic Fieldnames for View or Table

[PerBylund]

I'm working on creating a view (or table if a view isn't possible) based partly on the structured content of a table and partly on the dynamic structure in records in another table. The tables look like this:

Table A: ID, Name, Address, City, State, ZipCode
Table B: ID, Name, Value

Table A contains personal data like 1, 'Per Bylund', '1 My Street', 'My City', 'AA', '10000' and Table B contains records that are supposed to be fieldnames in the view (or table): 1, 'Country', 'USA'; 2, 'Phone', '800-800-8000' and so on. I wish to create a join where I use the Name, Address, City, State, and ZipCode fields from Table A and add the records from Table B as fieldnames. In this case, the view should look something like this:

ID, Name, Address, City, State, ZipCode, Country, Phone

The reason for this is that I will have a small number of groups of users with different information on users in different groups (but the same kind of information (structure-wise) for users in a group). Also, there is no way of foreseeing what groups will exist nor what kinds of information are used (the groups will be created and/or changed through a web interface by administrators).

I know creating a view each time a group is created or edited might be one way to handle this, but it would really help to be able to create a dynamic view. Any suggestions? Thanks!

 

[mrdenny]

That's your standard cross tab query.

select a.Name, a.Address, a.City, a.State, a.ZipCode,
   max(case when b.Name = 'Country' then b.value end) Country,
   max(case when b.Name = 'Phone' then b.value end)
from TableA a
join TableB b on a.ID = b.ID
group by a.Name, a.Address, a.City, a.State, a.ZipCode

Something like that should do the trick. If you have SQL 2005 you can use the PIVOT and UNPIVOT commands. In SQL 2000 you have to do it manually.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[PerBylund]

Thanks, I'm using SQL Server 2005 so I'll try to use the PIVOT command. Just have to understand how to use it.

 

[mrdenny]

You'll want to use something like this.

select a.*, b.*
from TableA a
join (
      select ID, [Country], [Phone]
      FROM (select ID, Value
            FROM TableB) p
      PIVOT
      (
      max(Value)
      FOR Name IN ('Country', 'Phone') 
      ) as pvt
) b on a.id = b.id

This hasn't been tested so it may need some tweaking.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com