SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HOW TO MAKE DYNAMIC PIVOT FOR THIS TABLES WHEN I PASS ONLY ONE PARAMETER @DID INT?


HOW TO MAKE DYNAMIC PIVOT FOR THIS TABLES WHEN I PASS ONLY ONE PARAMETER @DID INT?

Author
Message
Sivaganesh Tamilvendhan
Sivaganesh Tamilvendhan
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 1399
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
EMPLOYEEID SIVA
EMPLOYEENAME SIA001
MOBILENUMBER 9344139991
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25881 Visits: 17511
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sivaganesh Tamilvendhan
Sivaganesh Tamilvendhan
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 1399
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25881 Visits: 17511
sivajii (4/18/2013)
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


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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search