Dynamic Fieldnames for View or Table
-
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!
-
I think I understand what you are asking:
Will you be using this solution to retrive info for only one ID value at a time? If you are using it to return multiple IDs, will they always have the same name/value pairs in table B?
You could pivot the results of table B in a subquery and join that to table A in a PROC.
Post some DDL and sample data and I'll throw together an example.
SQL guy and Houston Magician
-
Robert Cary asked:
Will you be using this solution to retrive info for only one ID value at a time?
No. Probably not.
If you are using it to return multiple IDs, will they always have the same name/value pairs in table B?
Yes. A certain ID will belong to a group that might change its composition of "properties" but only anually or semiannually (=not very often).
-
This is a bit of a hack, but you could try this:
--Create example table structure CREATE TABLE #tableA ( ID INT PRIMARY KEY CLUSTERED, CustName VARCHAR(50), Address VARCHAR(200), Telephone VARCHAR(12), UserGroup INT ) CREATE TABLE #TableB ( ID INT NOT NULL, [Name] VARCHAR(40) NOT NULL, [Value] VARCHAR(40) ) --I know not to name constraints on temp tables. This is just an example ALTER TABLE #TableB ADD CONSTRAINT PKC__ID_Name PRIMARY KEY CLUSTERED (ID, [Name]) ON [PRIMARY] --INSERT Sample Data INSERT INTO #TableA(ID, CustName, Address, telephone, UserGroup) VALUES(1, 'Per Bylund', '1 your Street', '303-555-1212', 1) INSERT INTO #TableA(ID, CustName, Address, telephone, UserGroup) VALUES(2, 'Robert Cary', '1 my street', '720-555-1212', 1) INSERT INTO #TableB(ID, [Name], [Value]) VALUES(1, 'Column1', '123456') INSERT INTO #TableB(ID, [Name], [Value]) VALUES(1, 'Column2', '987654') INSERT INTO #TableB(ID, [Name], [Value]) VALUES(2, 'Column1', '112358') INSERT INTO #TableB(ID, [Name], [Value]) VALUES(2, 'Column2', '314159265') ------------------ DECLARE @Sql VARCHAR(5000) --Var for dynamic SQL DECLARE @Columns VARCHAR(1000) --Columns to use from Name/Value Table DECLARE @group INT ; SET @group = 1 --Would be an input parameter used in the query --Generate a list of columns that apply to this set of data SELECT @Columns = COALESCE(@Columns + ', ', '') + t2.[Name] FROM #TableB T2 INNER JOIN #tableA T1 ON t2.id = t1.id AND t1.UserGroup = @group GROUP BY t2.[Name] --Build the SQL statement. SET @Sql = 'SELECT t1.CustName, T1.Address, T1.Telephone, T2.' + REPLACE(@Columns, ', ', ', t2.') + ' FROM #TableA T1 INNER JOIN (SELECT * FROM #TableB PIVOT(MIN([Value]) FOR [Name] IN (' + @columns + ')) P) t2 ON t1.ID = T2.ID ' --Return the data EXEC(@Sql) --Clean Up DROP TABLE #tablea DROP TABLE #tablebSQL guy and Houston Magician
-
Thanks. Would it be possible to use the PIVOT command to achieve a "combined" table? I've heard elsewhere that it is an excellent feature in SQL 2005.
-
Sorry, I didn't realize you actually use the PIVOT command in your code.
-
I'm afraid I'm still having problems. Robert Cary's code works just great, but when replacing his table names with my own it doesn't work any more. I somehow get each value from "tableB" on a new row, which means I get as many records for each "tableA" as there are fields added. In my test data, where TableA = Person and TableB = PersonStat, and Person has only one record and PersonStat has 33, it means I get a result set of 33 rows/records. I should get only one.
I can't see what's wrong. The code I'm using is exactly the same as Robert's; I've only replaced the table and fieldnames. So I see no other solution than to paste my tables here. If you can help me out it would be GREATLY appreciated.
The query involves two tables where table Person includes personal info such as addresses and PersonStat includes a certain number of statistical pieces of information. The test data I'm using has only one record in table Person (I've tested with more with the same errors thrown) with 41 columns including primary key and update/creat dates, but it has 33 records in the PersonStat table. Thus, the result set should show one record with 74 fields/columns. The problem is that I get 33 records with 74 columns with the values from PersonStat distributed on individual rows (except for a few that exist on all...).
I've enclosed the tables as CREATE clauses and the content of PersonStats below. They are connected by the value URN which is not a key in any way, but an identifier for a person (there can exist doubles, but not, of course, in the test data since there's only one record). The query I'm using is:
DECLARE @Sql VARCHAR(5000) --Var for dynamic SQL
DECLARE @Columns VARCHAR(1000) --Columns to use from Name/Value Table
--Generate a list of columns that apply to this set of data
SELECT @Columns = COALESCE(@Columns + ', ', '') + t2.StatName
FROM PersonStat T2
INNER JOIN Person T1
ON --t2.id = t1.id AND
t1.URN = t2.URN
GROUP BY t2.StatName
--Build the SQL statement.
SET @Sql = '
SELECT T1.*, T2.' + REPLACE(@Columns, ', ', ', t2.') + '
FROM Person T1
JOIN
(
SELECT * FROM PersonStat
PIVOT
(
MAX(StatValue)
FOR StatName IN (' + @columns + ')
) AS P
) t2
ON t1.URN = T2.URN '
--Return the data
EXEC(@Sql)
And the tables:
CREATE TABLE [dbo].[Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SourceId] [int] NOT NULL,
[URN] [char](9) COLLATE NOT NULL,
[CustomerNo] [char](15) COLLATE NULL,
[IsPerson] [bit] NULL CONSTRAINT [DF_Person_IsPerson] DEFAULT ((1)),
[SSN] [nvarchar](12) COLLATE NULL,
[LastName1] [nvarchar](35) COLLATE NULL,
[FirstName1] [nvarchar](35) COLLATE NULL,
[FullName1] [nvarchar](35) COLLATE NULL,
[CareOf1] [nvarchar](35) COLLATE NULL,
[StreetAddress1] [nvarchar](35) COLLATE NULL,
[City1] [nvarchar](30) COLLATE NULL,
[ZipCode1] [nvarchar](5) COLLATE NULL,
[Country1] [nvarchar](20) COLLATE NULL,
[Phone_Home1] [nvarchar](15) COLLATE NULL,
[HouseCode11] [char](13) COLLATE NULL,
[HouseCode12] [char](4) COLLATE NULL,
[LastName2] [nvarchar](35) COLLATE NULL,
[FirstName2] [nvarchar](35) COLLATE NULL,
[FullName2] [nvarchar](35) COLLATE NULL,
[CareOf2] [nvarchar](35) COLLATE NULL,
[StreetAddress2] [nvarchar](35) COLLATE NULL,
[City2] [nvarchar](30) COLLATE NULL,
[ZipCode2] [nvarchar](5) COLLATE NULL,
[Country2] [nvarchar](20) COLLATE NULL,
[Phone_Home2] [nvarchar](15) COLLATE NULL,
[HouseCode21] [char](13) COLLATE NULL,
[HouseCode22] [char](4) COLLATE NULL,
[Email] [nvarchar](60) COLLATE NULL,
[WebSite] [nvarchar](60) COLLATE NULL,
[Organization] [nvarchar](50) COLLATE NULL,
[Position] [nvarchar](20) COLLATE NULL,
[Phone_Work] [nvarchar](15) COLLATE NULL,
[Phone_Cell] [nvarchar](15) COLLATE NULL,
[CNAi] [nvarchar](9) COLLATE NULL,
[Status_Person] [nvarchar](10) COLLATE NULL,
[Status_Address1] [nvarchar](10) COLLATE NULL,
[Status_Address2] [nvarchar](10) COLLATE NULL,
[Notes] [nvarchar](1024) COLLATE NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_Person_CreateDate] DEFAULT (getdate()),
[UpdateDate] [datetime] NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[PersonStat](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatId] [int] NULL,
[URN] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StatName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StatValue] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UpdateDate] [smalldatetime] NOT NULL CONSTRAINT [DF_PersonStat_UpdateDate] DEFAULT (getdate()),
CONSTRAINT [PK_PersonStat] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
And the content of PersonStat:
ID StatId URN StatName StatValue UpdateDate ----------- ----------- --------- ------------------------------ ---------- ----------------------- 1 NULL 000000000 Sex 1 2007-01-02 20:46:00 2 NULL 000000000 BornOnYear 1975 2007-01-02 20:47:00 3 NULL 000000000 Education University 2007-01-02 20:47:00 4 NULL 000000000 Income 480000 2007-01-02 20:47:00 5 NULL 000000000 Region Stockholm 2007-01-02 20:48:00 6 NULL 000000000 Living V 2007-01-02 20:48:00 7 NULL 000000000 Municipality Svlv 2007-01-02 20:48:00 8 NULL 000000000 Parish N Skrvl 2007-01-02 20:49:00 9 NULL 000000000 Segment11 1 2007-01-02 20:49:00 10 NULL 000000000 Segment12 1 2007-01-02 20:49:00 11 NULL 000000000 Segment13 0 2007-01-02 20:49:00 12 NULL 000000000 Segment14 1 2007-01-02 20:49:00 13 NULL 000000000 Segment15 0 2007-01-02 20:49:00 14 NULL 000000000 Segment16 1 2007-01-02 20:49:00 15 NULL 000000000 Segment17 1 2007-01-02 20:49:00 16 NULL 000000000 Segment18 1 2007-01-02 20:49:00 17 NULL 000000000 Segment19 0 2007-01-02 20:49:00 18 NULL 000000000 Segment20 0 2007-01-02 20:49:00 19 NULL 000000000 Segment21 0 2007-01-02 20:50:00 20 NULL 000000000 Segment22 1 2007-01-02 20:50:00 21 NULL 000000000 Segment24 1 2007-01-02 20:50:00 22 NULL 000000000 Segment27 1 2007-01-02 20:50:00 23 NULL 000000000 Segment29 1 2007-01-02 20:50:00 24 NULL 000000000 Segment31 0 2007-01-02 20:50:00 25 NULL 000000000 Segment32 1 2007-01-02 20:50:00 26 NULL 000000000 Segment33 1 2007-01-02 20:50:00 27 NULL 000000000 Segment34 0 2007-01-02 20:50:00 28 NULL 000000000 Segment35 1 2007-01-02 20:50:00 29 NULL 000000000 Segment36 1 2007-01-02 20:50:00 30 NULL 000000000 Segment99 1 2007-01-02 20:50:00 31 NULL 000000000 Segment999 0 2007-01-02 20:50:00 32 NULL 000000000 RefNo 1 2007-01-02 20:50:00 35 NULL 000000000 WeekendCottage 0 2007-01-02 21:17:00 (33 row(s) affected)
-
just a quick thought:
try replaceing
SELECT * FROM PersonStat
with
SELECT URN, StartName, StartValue FROM PersonStat
SQL guy and Houston Magician
-
No, that doesn't work at all
:DECLARE @Sql VARCHAR(5000) --Var for dynamic SQL
DECLARE @Columns VARCHAR(1000) --Columns to use from Name/Value Table
--Generate a list of columns that apply to this set of data
SELECT @Columns = COALESCE(@Columns + ', ', '') + t2.StatName
FROM PersonStat T2
INNER JOIN Person T1
ON --t2.id = t1.id AND
t1.URN = t2.URN
GROUP BY t2.StatName
--Build the SQL statement.
--SET @Sql = '--CREATE VIEW TESTING_PIVOT AS (
SET @Sql = '
SELECT T1.*, T2.' + REPLACE(@Columns, ', ', ', t2.') + '
FROM Person T1
JOIN
(
SELECT ID, URN, StatName, StatValue FROM PersonStat
PIVOT
(
MAX(StatValue)
FOR StatName IN (' + @columns + ')
) AS P
) t2
ON t1.URN = T2.URN '
--)'
--print @sql
--Return the data
EXEC(@Sql)
Error message(s):
Msg 207, Level 16, State 1, Line 6
Invalid column name 'StatName'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'StatValue'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'BornOnYear'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Education'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Income'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Living'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Municipality'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Parish'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'RefNo'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Region'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment11'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment12'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment13'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment14'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment15'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment16'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment17'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment18'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment19'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment20'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment21'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment22'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment24'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment27'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment29'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment31'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment32'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment33'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment34'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment35'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment36'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment99'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Segment999'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Sex'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'WeekendCottage'.
-
Of course, sorry! When building the PIVOT result set, SQL Server automatically adds a GROUP BY clause that groups by all the columns from the table_source that are not either the value_column or the pivot_column. Due to this grouping, the table_source should contain only columns that are of interest in the final query. You could achieve this using a View containing only the relevant columns, but using a CTE or a subquery serves the same purpose.
e.g.
Replace
SELECT ID, URN, StatName, StatValue FROM PersonStat
with
SELECT * FROM (SELECT URN, StatName, StatValue FROM PersonStat) P
and it will work.
SQL guy and Houston Magician
-
EXCELLENT! Thanks!
-
No Problem, I'm glad I could help!
SQL guy and Houston Magician
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply