sivajii (4/18/2013)
i go throughed the links Sean Langethey are fine but i am not able to implement this kind of dynamic solution for my store procedure
so plz tell me the exact solution for my store procedure
I think you could greatly simplify your original query to this.
SELECT d.VALUE, cd.DETAILSVALUE
FROM @DETAILS d
join @CONTACTDETAILS cd on cd.CONTACTID = d.CONTACTID
where cd.DID = 1
order by d.CONTACTID
There is no need to do a correlated subquery like you did.
Now onto your issue:
What you have is an Entity-Attribute-Value. This type of structure is real challenge to work with. I am a little nervous that if you can't figure out how to turn the example into something you can use that you don't understand what is going on. If you don't understand it, how can you support it?
I will show how you can use a static cross tab for this.
SELECT MAX(case when ContactID = 1 then DETAILSVALUE else '' end) as PhoneNumber
, MAX(case when ContactID = 2 then DETAILSVALUE else '' end) as MobileNumber
, MAX(case when ContactID = 3 then DETAILSVALUE else '' end) as Address1
, MAX(case when ContactID = 4 then DETAILSVALUE else '' end) as Address2
, MAX(case when ContactID = 5 then DETAILSVALUE else '' end) as EmployeeName
, MAX(case when ContactID = 6 then DETAILSVALUE else '' end) as EmployeeID
FROM @CONTACTDETAILS
Where DID = 1
See if this might get you started on being able to turn this into the dynamic version.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/