February 23, 2009 at 5:22 am
Hey folks,
I have a pivoted view, whose columns are based on the entries of a table.
example:
--------------------------TABLES
CREATE TABLE [tblBerufskuerzel](
[Berufskuerzel] [nvarchar](10) NOT NULL PRIMARY KEY,
[Beschreibung] [nvarchar](50) NULL)
GO
CREATE TABLE [tblK_Employment](
[ID_Employment] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[ID_Kandidat] [int] NULL,
[Berufskuerzel] [nvarchar](10) NULL)
GO
--------------------------VALUES
INSERT INTO [tblBerufskuerzel] VALUES ('***','Assurance, Versicherungen, Rück-Vers.')
INSERT INTO [tblBerufskuerzel] VALUES ('CIMP','Consulting: Implementierung')
INSERT INTO [tblBerufskuerzel] VALUES ('CSTR','Consulting: Strategie')
INSERT INTO [tblBerufskuerzel] VALUES ('ECOM','E-Commerce, neue Medien')
GO
INSERT INTO [tblK_Employment] VALUES (100, '***')
INSERT INTO [tblK_Employment] VALUES (100, 'CIMP')
INSERT INTO [tblK_Employment] VALUES (101, '***')
INSERT INTO [tblK_Employment] VALUES (102, '***')
INSERT INTO [tblK_Employment] VALUES (103, 'CIMP')
INSERT INTO [tblK_Employment] VALUES (103, 'CSTR')
INSERT INTO [tblK_Employment] VALUES (107, 'ECOM')
INSERT INTO [tblK_Employment] VALUES (107, 'CIMP')
INSERT INTO [tblK_Employment] VALUES (108, 'ECOM')
INSERT INTO [tblK_Employment] VALUES (109, 'ECOM')
GO
--------------------------VIEW
CREATE VIEW [qryPvtK_EmploymentBerufskuerzel]
AS
SELECT ID_Kandidat, ***, CIMP, CSTR, ECOM
FROM (SELECT ID_Kandidat, Berufskuerzel
FROM tblK_Employment) KB PIVOT (COUNT(Berufskuerzel)
FOR Berufskuerzel IN (***, CIMP, CSTR, ECOM) )
AS qryPvtK_EmploymentBerufskuerzel
GO
--------------------------SELECT
SELECT * FROM [qryPvtK_EmploymentBerufskuerzel]
--------------------------DROP
DROP VIEW [qryPvtK_EmploymentBerufskuerzel]
DROP TABLE [tblBerufskuerzel]
DROP TABLE [tblK_Employment]
If there gets another Berufskuerzel inserted into the table tblBerufskuerzel (this occurs very rarely) the view qryPvtK_EmploymentBerufskuerzel should get altered to contain the new Berufskuerzel.
So I need a way to have a list of colums in the SELECT statement which originates from the result of
SELECT Berufskuerzel FROM tblBerufskuerzel
was that understandable?
and far more interesting: is that possible?
😀
February 23, 2009 at 9:42 am
It is possible and a bit complex but don't get discouraged 🙂
The First thing is to track the "new" values you probably want to isolate those values in a lookup table.
Then add a FK to your main table.
Once there every update or insert must be monitored by a trigger which executes two command with dynamic sql
the first runs an ALTER VIEW to fix the view contents and the second is sp_refreshview to fix the possibly cached metadata.
There are other ways to do this though.
Instead of a view use straight dynamic sql to build the statement on the fly either from the client or from an stored procedure.
Hope this helps!
* Noel
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply