none
Display data in columns in sql server RRS feed

  • Question

  • Hello All,

    I have a query where I am getting data for a case like below.

    My properties data coming from properties table which have multiple properties for each case.

    CaseNo Type Requestor Properties Name Property Value
    24868145 Request Sam EmpNo 12345
    24868145 Request Sam Mobile Number 9898909890
    24868145 Request Sam  address India
    24868145 Request Sam Asset Required Laptop

    My requirement would be like below.

    Instead of showing  rows for 4 properties I need to show only one row and show properties as columns like below

    Please help

    CaseNo Type Requestor EmpNo Mobile Number  address Asset Required
    24868145 Request Sam 12345 9898909890 India Laptop

    • Moved by Naomi NModerator Tuesday, June 23, 2020 7:58 PM Better answer can be here
    Tuesday, May 19, 2020 1:55 PM

All replies

  • Hi friend,

    Using PIVOT will be simpler.

    PIVOT syntax needs to use the aggregation function. In this scenario, you can use the max () or min () function.But you need to ensure that for each Requestor, "Properties Name" and "Property Value" are in one-to-one correspondence.

    SELECT *
    FROM
    (
    SELECT [CaseNo]
          ,[Type]
          ,[Requestor]
          ,[Properties Name]
          ,[Property Value]
      FROM [test].[dbo].[Info]
    ) AS SourceTable PIVOT(max([Property Value]) FOR [Properties Name] IN([EmpNo],
                                                             [Mobile Number],
                                                             [address],
                                                             [Asset Required])) AS PivotTable;

    In addition to pivot, there are other options:Use an aggregate function or multiple joins. you can refer to this nice post.

    Best Regards,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 20, 2020 2:34 AM
  • Hi friend,

    Was your issue resolved?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 21, 2020 1:15 AM