SQL Server 2014 change multiple string rows to columns

Question

I have below data and it will have multiple titles against multiple genres. Every genre has 7 top titles.

Genre | MarketingTitle 
------+----------------
Drama | Drama 1
Drama | Drama 2
...
Drama | Drama 7

I want output to look something like

Genre  | Title 1 | Title 2 | Title 3 | ... | Title7
-------+---------+---------+---------+-----+---------
Drama  | Drama1  | Drama2  | Drama3  | ... | Drama7
Comedy | Comedy1 | Comedy2 | Comedy3 | ... | Comedy7

I tried pivot table but its just not working

Select 
    GenreName, [Drama], [Comedy]
from
    (select 
         g.name as GenreName, p.MarketingTitle as MarketingTitle
     from 
         programme p
     inner join 
         Genre g on g.Id = p.GenreId
     where 
         topTitle = 1) c
pivot 
    (max(MarketingTitle) 
        for MarketingTitle in ([Drama], [Comedy])
    ) As pvt

Everything is returned as null and I am pretty sure this query is wrong.

Even below output is desirable but I cant seem to make query work. any help is appreciated.

Drama   | Comedy  | ... | otherGenres
--------+---------+-----+------------
drama1  | comedy1 | ... |
drama2  | comedy2 | ... |
.. .
drama7  | comedy7 | ... |

uzi · Accepted Answer · 2018-02-17 15:31:47Z

1

Try conditional aggregation instead

select
    GenreName, Title1 = max(case when rn = 1 then MarketingTitle end)
    , Title2 = max(case when rn = 2 then MarketingTitle end)
    , Title3 = max(case when rn = 3 then MarketingTitle end)
    , Title4 = max(case when rn = 4 then MarketingTitle end)
    , Title5 = max(case when rn = 5 then MarketingTitle end)
    , Title6 = max(case when rn = 6 then MarketingTitle end)
    , Title7 = max(case when rn = 7 then MarketingTitle end)
from (
    select g.name as GenreName, p.MarketingTitle as MarketingTitle
        , rn = row_number() over (partition by g.name order by p.MarketingTitle)
    from programme p
    inner join Genre g on g.Id = p.GenreId
    where top = 1
) t
group by GenreName

answered Feb 17, 2018 at 15:31

uzi

4,1561 gold badge18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server 2014 change multiple string rows to columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related