View to include Columns based on a Dynamically Generated Query

[Michael Cole]

This is a bit of a weird one.

We have tables along the lines of the following design (note - not our
design, please don't tell me to change it): -


CREATE TABLE [dbo].[ZColumns](
[ID] [int] NOT NULL,
[ColumnID] [int] NOT NULL,
[Table] [varchar](50) NULL,
[Column] [nvarchar](50) NULL,
CONSTRAINT [PK_ZColumns] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[ColumnID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ZTab00001](
[ID] [int] NULL,
[001] [varchar](50) NULL,
[002] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ZTab00002](
[ID] [int] NULL,
[001] [varchar](50) NULL,
[002] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ZData](
[ID] [int] NULL,
[Data1] [nvarchar](50) NULL,
[Data2] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [ZColumns] VALUES (1, 1, '00001', '001');
INSERT INTO [ZColumns] VALUES (1, 2, '00001', '002');
INSERT INTO [ZColumns] VALUES (2, 1, '00001', '001');
INSERT INTO [ZColumns] VALUES (2, 2, '00002', '002');
INSERT INTO [ZColumns] VALUES (3, 1, '00002', '001');

INSERT INTO [ZTab00001] VALUES (1, 'G', 'H');
INSERT INTO [ZTab00001] VALUES (2, 'I', 'J');
INSERT INTO [ZTab00001] VALUES (3, 'K', 'L');

INSERT INTO [ZTab00002] VALUES (1, 'M', 'N');
INSERT INTO [ZTab00002] VALUES (2, 'O', 'P');
INSERT INTO [ZTab00002] VALUES (3, 'Q', 'R');

INSERT INTO [ZData] VALUES (1, 'A', 'B');
INSERT INTO [ZData] VALUES (2, 'C', 'D');
INSERT INTO [ZData] VALUES (3, 'E', 'F');



I would like to create a view, that would return the following: -
ID Data1 Data2 Col1 Col2
1 A B G H
2 C D I P
3 E F Q Null

In other words, link to the data for Col1 and Col2 based off a
determination of the table and field from the table ZColumns.

Are there any thoughts as to how this could be done?

[Erland Sommarskog]

Post by Michael Cole
I would like to create a view, that would return the following: -
ID Data1 Data2 Col1 Col2
1 A B G H
2 C D I P
3 E F Q Null
In other words, link to the data for Col1 and Col2 based off a
determination of the table and field from the table ZColumns.
Are there any thoughts as to how this could be done?

WITH pivotedZ AS (
SELECT ID,
MIN(CASE ColumnID WHEN 1 THEN [Table] END) AS Table1,
MIN(CASE ColumnID WHEN 1 THEN [Column] END) AS Col1,
MIN(CASE ColumnID WHEN 2 THEN [Table] END) AS Table2,
MIN(CASE ColumnID WHEN 2 THEN [Column] END) AS Col2
FROM ZColumns
GROUP BY ID
)
SELECT Z.ID, D.Data1, D.Data2,
CASE WHEN Z.Table1 = '00001' AND Z.Col1 = '001' THEN T1.[001]
WHEN Z.Table1 = '00001' AND Z.Col1 = '002' THEN T1.[002]
WHEN Z.Table1 = '00002' AND Z.Col1 = '001' THEN T2.[001]
WHEN Z.Table1 = '00002' AND Z.Col1 = '002' THEN T2.[002]
END AS Col1,
CASE WHEN Z.Table2 = '00001' AND Z.Col2 = '001' THEN T1.[001]
WHEN Z.Table2 = '00001' AND Z.Col2 = '002' THEN T1.[002]
WHEN Z.Table2 = '00002' AND Z.Col2 = '001' THEN T2.[001]
WHEN Z.Table2 = '00002' AND Z.Col2 = '002' THEN T2.[002]
END AS Col2
FROM pivotedZ Z
LEFT JOIN ZData D ON Z.ID = D.ID
LEFT JOIN ZTab00001 T1 ON Z.ID = T1.ID
LEFT JOIN ZTab00002 T2 ON Z.ID = T2.ID

And if you tell me that you have umpteen of these ZTab000x tables,
I can you only wish you good luck. Bad database designs often leads
very difficult where you get lost in a morass of dynamic SQL.

[Michael Cole]

Post by Erland Sommarskog
And if you tell me that you have umpteen of these ZTab000x tables,
I can you only wish you good luck. Bad database designs often leads
very difficult where you get lost in a morass of dynamic SQL.

Unfortunately, there are. There are 20 of these tables to deal with,
and the app can dynamically create more - basically once a table gets
10,000 records, the app creates another table. Its an incredibly
painful system to get into.

We are currently using dynamic SQL, as it was the only way we could
figure it out, but that means that it can't be in a View, only in an
SP. I was hoping that we could do this without dynamic SQL.

[Erland Sommarskog]

Post by Michael Cole
We are currently using dynamic SQL, as it was the only way we could
figure it out, but that means that it can't be in a View, only in an
SP. I was hoping that we could do this without dynamic SQL.

No. A view is a query, and a query is static in what sources it access and
which columns it produces.

One thing you could do to mitigate this complete craziness is to have a view
which is a UNION ALL of these tables. You would then have a DDL trigger so
that each time this crazy application creates a new table, you change the
view. However, a query can only refer to 1024 tables, so there is a risk
that you hit this limit.

Another alternative is to have the DDL trigger to drop the table, and
replace it with a view with an instead of trigger that inserts data into the
real table.

Of course, if this a third-party application, that would void all support
obligations for the vendor. But I am not sure I would like to get any
support from a vendor which is so grossly incompetent.

[Michael Cole]

Post by Erland Sommarskog
Of course, if this a third-party application, that would void all support
obligations for the vendor. But I am not sure I would like to get any
support from a vendor which is so grossly incompetent.

The amusing thing from our POV is that this third-party application is
a Help Desk application, which we are trying to use to support _our_
applications and users. We have an Application Support application
which is badly supported. We have a Help Desk application which is not
helpful. Fun...