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

  • 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

  • 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/

  • 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

  • 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 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