Replace view by join

Hi all,

I have a list of Clients like:
Table_Client

ID Name 10 Bill 11 Frank 12 Carl 13 Rita 14 Jan 15 Bonny 16 Bart 17 George 18 Ann

Now I want to ad some variable data to each client, so I have created a second table like:

Table_Client_Data

ID ClientID FieldName FieldDataString FieldDataInt FieldDataDate FieldDateBoolean 2 10 CustomerNr g146 4 11 CustomerNr g121 5 12 CustomerNr g147 6 13 CustomerNr g236 7 15 CustomerNr g245 9 10 Dog yes 10 11 Dog No 10 12 Dog yes 10 13 Dog No 10 15 Dog yes

Now I want to have the next table as result in one query:

ID Name CustomerNr Dog 10 Bill g146 yes 11 Frank g121 No 12 Carl g147 yes 13 Rita g236 No 14 Jan 15 Bonny g245 yes 16 Bart 17 George 18 Ann

First I have made a VIEW to create, I had used a inner Join

SELECT Table_Client.ID,
Table_Client_Data.FieldDataString AS CustomerNr,
Table_Client_Data_1.FieldDataString AS Dog

FROM Table_Client_Data

INNER JOIN
Table_Client_Data ON Table_Client.ID = Table_Client_Data.ClientID
INNER JOIN
Table_Client_Data as Table_Client_Data_1 ON Table_Client.ID = Table_Client_Data_1.ClientID

WHERE (Table_Client_Data.FieldName = 'CustomerNr') AND (Table_Client_Data_1.FieldName = 'Dog')

View_CliuentData

ID CustomerNr Dog 10 g146 yes 11 g121 No 12 g147 yes 13 g236 No 15 g245 yes

Now I join View_CliuentData with Table_Client and I have the right result.

Now my question,
Is there any way to skip the View an do this all in a join. I have tried several things but ... no result.

Tks Bart

Hi bart,

I′m thinking about your problem, and one way to solve this problem, are you using the temporary tables.

All data are copied to temporary tables and you make a join with this object.

The best thing about this, are no necessary physical objects stored in bank.

|||Hello,

I'm not using temporary tables.
But will this table be regenerated every time I do a request?
If yes: this will not be so performant as a view I guess.
Is there no way to do this in one query.

Gr Bart
|||Would it not be more extensible and performant to use a single XML type to represent your data? It would still be searchable, fields would be stored by their appropriate type, and association would be explicit while extensibility of the type is guaranteed generally by the XML specification.|||

First, let me say that is a very strange design, and one that will prove to continually be a problem. As with most denormalized databases, maintenance and scaling could prove to be a nightmare.

However, this suggestion provides your desired results from a single query.

Code Snippet

SET NOCOUNT ON

DECLARE @.Clients table
( [ID] int,
[Name] varchar(20)
)

INSERT INTO @.Clients VALUES ( 10, 'Bill' )
INSERT INTO @.Clients VALUES ( 11, 'Frank' )
INSERT INTO @.Clients VALUES ( 12, 'Carl' )
INSERT INTO @.Clients VALUES ( 13, 'Rita' )
INSERT INTO @.Clients VALUES ( 14, 'Jan' )
INSERT INTO @.Clients VALUES ( 15, 'Bonny' )
INSERT INTO @.Clients VALUES ( 16, 'Bart' )
INSERT INTO @.Clients VALUES ( 17, 'George' )
INSERT INTO @.Clients VALUES ( 18, 'Ann' )

DECLARE @.ClientsData table
( [ID] int,
ClientID varchar(10),
FieldName varchar(10),
FieldStr varchar(20),
FieldInt int,
FieldDate datetime,
FieldBool bit
)

INSERT INTO @.ClientsData VALUES ( 2, 10, 'CustomerNr', 'g146', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 4, 11, 'CustomerNr', 'g121', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 5, 12, 'CustomerNr', 'g147', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 6, 13, 'CustomerNr', 'g236', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 7, 15, 'CustomerNr', 'g245', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 9, 10, 'Dog', 'yes', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 10, 11, 'Dog', 'No', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 10, 12, 'Dog', 'yes', NULL, '',NULL )
INSERT INTO @.ClientsData VALUES ( 10, 13, 'Dog', 'No', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 10, 15, 'Dog', 'yes', NULL, '', NULL )

SELECT
c.[ID],
CustomerNr = CASE cd1.FieldName WHEN 'CustomerNr' THEN cd1.FieldStr END,
Dog = CASE cd2.FieldName WHEN 'Dog' THEN cd2.FieldStr END
FROM @.Clients c
JOIN @.ClientsData cd1
ON c.[ID] = cd1.ClientID
AND cd1.FieldName = 'CustomerNr'
JOIN @.ClientsData cd2
ON c.[ID] = cd2.ClientID
AND cd2.FieldName = 'Dog'

ID CustomerNr Dog
-- -- --
10 g146 yes
11 g121 No
12 g147 yes
13 g236 No
15 g245 yes