Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

HOW TO MAKE DYNAMIC PIVOT FOR THIS TABLES WHEN I PASS ONLY ONE PARAMETER @DID INT? Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 6:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 1:47 PM
Points: 211, Visits: 1,369
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
Post #1443233
Posted Wednesday, April 17, 2013 8:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 12,016, Visits: 11,046
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 Moden's 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)
Post #1443300
Posted Thursday, April 18, 2013 12:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 1:47 PM
Points: 211, Visits: 1,369
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
Post #1443621
Posted Thursday, April 18, 2013 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 12,016, Visits: 11,046
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 Moden's 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)
Post #1443889
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse