Dear helper,
I have the question of T-SQL.
I have a table original is:
and want to use Sql to make it becomes:
Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 2.24 kg Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Water-reducing Admixture
It is better to make it becomes a view for table joining.
Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding, Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding, Water-reducing Admixture
Regards,
Man Pak Hong, Dave
try this..SELECT a.Concrete_Grade
, a.Mix_Code
, a.RM_ID
, a.RM_Name
, a.RM_Value
, a.UnitType_ID
, a.RMTypeType_Name
, b.RMType_Name
FROM YourTable a INNER JOIN
YourTable b ON a.Concrete_Grade = b.Concrete_Grade
AND a.Mix_Code = b.Mix_Code
AND a.RM_ID = b.RM_ID
AND a.RM_Name = b.RM_Name
AND a.RM_Value = b.RM_Value
AND a.UnitType_ID = b.UnitType_ID|||
So your point is creating view which holds the values [RMTypeType_Name]='Water-reducing', lets say vw_MyData_WaterReducing. Later you want to join this view with outher tables on your query.
One way it is good if you use INDEXED VIEW. You have to create a index on this new view. It will increase the performance well.
But if you try to use with out index (only the filtered query), it may decrease the performance. You may unknowingly use Self join on your query...
To know better abotu indexed view visit here ... http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
