Adding Column While Selecting Table in SQl

Posted by kmkperumal on Stack Overflow See other posts from Stack Overflow or by kmkperumal
Published on 2012-11-03T10:44:24Z Indexed on 2012/11/03 11:00 UTC
Read the original article Hit count: 391

Filed under:
|

My First Table is ProjectCustomFields

CustomFieldId   ProjectId   CustomFieldName CustomFieldRequired CustomFieldDataType
69  1   User Name   1   0
72  1   City    1   0
74  1   Email   0   0
82  1   Salary  1   2

My Second Table is ProjectCustomFieldValues

CustomFieldValueId  ProjectId   CustomFieldId   CustomFieldValue    RecordId
35  1   69  kaliya  1
36  1   72  Bangalore   1
37  1   74  [email protected]    1
41  1   69  Yohesh  2
42  1   72  Delhi   2
43  1   74      2
50  1   69  sss 3
51  1   72  Delhi   3
52  1   74  [email protected]    3
57  1   69  Sunil   4
58  1   72  Mumbai  4
59  1   74  [email protected]  4
60  1   82  20000   4

I tried Below Query

Select M.CustomFieldName,N.CustomFieldValue,N.RecordId From
(Select G.CustomFieldName,H.RecordId From
(Select CustomFieldName From ProjectCustomFields Where ProjectId=1) G Cross Join
(Select Distinct RecordId From ProjectCustomFieldValues) H) M

Left Join 
(Select CustFiled.CustomFieldName,CustValue.CustomFieldValue,CustValue.RecordId From ProjectCustomFieldValues CustValue Left Join 
ProjectCustomFields CustFiled  On CustValue.CustomFieldId=CustFiled.CustomFieldId Where CustValue.AuctionId=1
) N On M.CustomFieldName=N.CustomFieldName And M.RecordId=N.RecordId

But I got the result below

#CustomFieldName#   CustomFieldValue    RecordId
User Name   kaliya  1
City    Bangalore   1
Email   [email protected]    1
Salary  NULL    **NULL**
User Name   Yohesh  2
City    Delhi   2
Email       2
Salary  NULL    **NULL**
User Name   sss 3
City    Delhi   3
Email   [email protected]    3
Salary  NULL    **NULL**
User Name   NULL    **NULL**
City    NULL    **NULL**
Email   NULL    **NULL**
Salary  NULL    **NULL**
User Name   Sunil   4
City    Mumbai  4
Email   [email protected]  4
Salary  20000   4

But Expected Result is

CustomFieldName CustomFieldValue    RecordId
User Name   kaliya  1
City    Bangalore   1
Email   [email protected]    1
Salary  NULL    **1**
User Name   Yohesh  2
City    Delhi   2
Email       2
Salary  NULL    **2**
User Name   sss 3
City    Delhi   3
Email   [email protected]    3
Salary  NULL    **3**
User Name   Sunil   4
City    Mumbai  4
Email   [email protected]  4
Salary  20000   4

Please guide me some one,I tried so much but i got null value in recordId,So I need same recordId above one..

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2008