April 17, 2013 at 6:48 am
here iam having two table @DETAILS and @CONTACTDETAILS in this two table @DETAILS is a static table in which cotactid will be the
primary key for the table @DETAILS and it will be the foreign key for the @CONTACTDETAILS and here this the tables with data
DECLARE @DETAILS TABLE (CONTACTID INT IDENTITY(1,1), VALUE VARCHAR(200))
INSERT INTO @DETAILS
SELECT 'PHONENUMBER' UNION ALL
SELECT 'MOBILENUMBER' UNION ALL
SELECT 'ADDRESS1' UNION ALL
SELECT 'ADDRESS2' UNION ALL
SELECT 'EMPLOYEENAME' UNION ALL
SELECT 'EMPLOYEEID'
DECLARE @CONTACTDETAILS TABLE (DETAILID INT IDENTITY(1,1), DID INT ,CONTACTID INT, DETAILSVALUE VARCHAR(200))
INSERT INTO @CONTACTDETAILS
SELECT 1,5,'SIVA' UNION ALL
SELECT 1,6,'SIA001' UNION ALL
SELECT 1,2,'9344139991' UNION ALL
SELECT 2,1,'245678' UNION ALL
SELECT 2,2,'1686597' UNION ALL
SELECT 2,5,'GANESH' UNION ALL
SELECT 1,3,'ST-ROAD'
SELECT * FROM @DETAILS
the output for this
CONTACTID VALUE
1 PHONENUMBER
2 MOBILENUMBER
3 ADDRESS1
4 ADDRESS2
5 EMPLOYEENAME
6 EMPLOYEEID
SELECT * FROM @CONTACTDETAILS
the output for this
DETAILID DID CONTACTID DETAILSVALUE
1 1 6 SIVA
2 1 5 SIA001
3 1 2 9344139991
4 2 1 245678
5 2 2 1686597
6 2 5 GANESH
7 1 3 ST-ROAD
in the @CONTACTDETAILS column iam having DID column which i used to show one particular employee detail in my stroe proc iam using
parameter @DID int i just tried this query
declare @DID int=1
SELECT
(SELECT VALUE FROM @DETAILS WHERE CONTACTID=A.CONTACTID)AS CONTACTID,
A.DETAILSVALUE
FROM
@CONTACTDETAILS A
WHERE
A.DID=@DID
which will show the result like this
CONTACTID DETAILSVALUE
EMPLOYEEIDSIVA
EMPLOYEENAMESIA001
MOBILENUMBER9344139991
ADDRESS1 ST-ROAD
and now i am trying output like this
EMPLOYEEID EMPLOYEENAME MOBILENUMBER ADDRESS1
SIA001 SIVA 9344139991 ST-ROAD
can any one plz help me
April 17, 2013 at 8:29 am
Any chance you can normalize your data? The problem you are having here is because you have a single column with all sort of data elements in it.
If you are unable to normalize your data you will need to do a cross tab. There are two articles in my signature that go into great detail about how to build these. I am kinda swamped right now but if I get a chance I will come back later and see if I can help.
_______________________________________________________________
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/
April 18, 2013 at 12:49 am
i go throughed the links Sean Lange
they 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
April 18, 2013 at 8:09 am
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply