Home Forums SQL Server 2008 SQL Server 2008 - General HOW TO MAKE DYNAMIC PIVOT FOR THIS TABLES WHEN I PASS ONLY ONE PARAMETER @DID INT? RE: HOW TO MAKE DYNAMIC PIVOT FOR THIS TABLES WHEN I PASS ONLY ONE PARAMETER @DID INT?

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