The problem is each column which is created by the case is doing it own ordering.

I am using a case statement to pivot details in my table.....

The problem is each column which is created by the case is doing it own ordering.

How can I put all the NULL values to the bottom.

Here is some dummy data of what I have now.

Shop ID      Shop Name      English      French      Spanish      German
11      Jam's      NULL      NULL      NULL      TestData
11      Jam's      NULL      NULL      NULL      NULL
11      Jam's      NULL      TestData      NULL      NULL
11      Jam's      NULL      TestData      NULL      NULL
11      Jam's      NULL      TestData      NULL      NULL
11      Jam's      TestData      NULL      NULL      NULL
11      Jam's      TestData      NULL      NULL      NULL
11      Jam's      TestData      NULL      NULL      NULL
11      Jam's      TestData      NULL      NULL      NULL
11      Jam's      TestData      NULL      NULL      NULL


Here is what I am after.

Shop ID      Shop Name      English      French      Spanish      German
11      Jam's      TestData      TestData      NULL      TestData
11      Jam's      TestData      TestData      NULL      NULL
11      Jam's      TestData      TestData      NULL      NULL
11      Jam's      TestData      NULL      NULL      NULL
11      Jam's      TestData      NULL      NULL      NULL

My code is bellow....

Thanks :)

select   [Shop ID] = H.ID,
            [Shop Name] = H.Name,
            
            CASE WHEN HFXC.LID = 1 THEN convert(NVARCHAR,HFXC.Comment) END AS [Facilities in English],
            CASE WHEN HFXC.LID = 2 THEN convert(NVARCHAR,HFXC.Comment) END AS [Facilities in French],
            CASE WHEN HFXC.LID = 3 THEN convert(NVARCHAR,HFXC.Comment) END AS [Facilities in Spanish],
            CASE WHEN HFXC.LID = 4 THEN convert(NVARCHAR,HFXC.Comment) END AS [Facilities in German]
           
   from     lrs.Shop H with ( nolock )
            left Join lrs.ShopFacility HFX With ( nolock ) on HFX.ShopID = H.ID
            left Join lrs.ShopFacilityComment HFXC With ( nolock ) on HFXC.ID = HFX.FacilityID
            left Join lrs.ShopStatus HS WITH ( NOLOCK ) ON HS.ID = H.StatusID
   where    HS.ID = 2 and h.ID = 105
   order by 3,4,5,6 desc

Select allOpen in new window