CODEWITHSUNDEEP
sqlsql-serverpivot
Josh
Josh

Reputation: 1058

Can't figure out how to pivot and merge these results

I have a query that returns data that looks like.

PackageName  SiteLocation  Visible
Package1     SiteOne       1
Package1     SiteTwo       0
Package1     SiteFour      1
Package2     SiteThree     1

What I am wanting is the data to look like

PackageName  SiteOne  SiteTwo  SiteThree  SiteFour
Package1     1        0        NULL       1
Package2     Null     Null     1          NULL

I did a pivot on Site but the results I get look like

PackageName  SiteOne  SiteTwo  SiteThree  SiteFour
Package1     1        NULL     NULL       NULL
Package1     NULL     0        NULL       NULL
Package1     NULL     NULL     NULL       1
Package2     Null     Null     1          NULL

Is there a way to merge pivot on site then merge the results of PackageName?

The query that returns this data is a simple query that just joins together foreign keys to turn SiteLocation into something meaningful instead of a FK_Id

Upvotes: 0

Views: 76

Answers (1)

Taryn
Taryn

Reputation: 247880

Without seeing your actual query, I am guessing that you have additional fields in your query that is breaking your PIVOT. If those are the only fields returned by your query, then it should be working:

select *
from
(
  select packagename,
    sitelocation,
    visible
  from yourquery
) x
pivot
(
  max(visible)
  for sitelocation in ([SiteOne], [SiteTwo], [SiteThree], [SiteFour])
) p

See SQL Fiddle with Demo

But if you have other fields included in your results, then the PIVOT will not work properly:

select *
from
(
  select packagename,
    sitelocation,
    visible, 
    breakit
  from yourquery
) x
pivot
(
  max(visible)
  for sitelocation in ([SiteOne], [SiteTwo], [SiteThree], [SiteFour])
) p

See SQL Fiddle with Demo

Upvotes: 2

Related Questions