CODEWITHSUNDEEP
sql-server
SaiBand
SaiBand

Reputation: 5355

PIVOT result set in SQL SERVER 2008

I have a result set like this:

SELECT PropertyId, Owner from Table A

PropertyId    Owner

  1          Company A
  1          Company B

I want to Pivot the result set like this:

PropertyId  Owner 1 Owner 2

 1           CompanyA Company B

In other words I want 2 owners for every property. Assume that every property has at the max 2 owners.

Upvotes: 0

Views: 469

Answers (2)

Brad
Brad

Reputation: 635

Only problem with the query I created is if there is only 1 owner it will not display the propertyid, but it will work with null values.

;With [CTE] as (
  Select
    [PropertyId]
    ,[Owner]
    ,Row_Number()
      Over(Partition by [PropertyId] Order by [Owner]) as [RN]
  From [TableA]
)
Select
  a.[PropertyId]
  ,a.[Owner] as [Owner1]
  ,b.[Owner] as [Owner2]
From [CTE] as a
    Left Join [CTE] as b on a.[PropertyId] = b.[PropertyId]
        And b.[RN] = 2
Where a.[RN] = 1
    --And b.[RN] = 2

Edit: Updated to show b.[RN] = 2 in join statement as suggested. Updated SQL Fiddle

SQL Fiddle: http://sqlfiddle.com/#!3/5af8c/7

Upvotes: 2

CandiedCode
CandiedCode

Reputation: 604

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

...

[last pivoted column] AS <column name>

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(

**<aggregation function>(<column being aggregated>)**

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

I don't think you can pivot because you aren't doing any aggregations

Upvotes: 0

Related Questions