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

Open in new window

Mr_ShawAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
To get NULLS sorted toward the bottom, then simply add the DESC qualifier ie:

Order by 3 desc, 4 desc, 5 desc, 6 desc

But your sample output seems to be "grouping" as well, but not sure why or what would really differentiate any of the subsequent rows from the first row - they are all the same pieces of data, so doesnt quite make sense why there needs to be the number of rows to accommodate the max number of instances of any one column.

Kinda like a rollup...

Certainly the PIVOT would consolidate all that onto the one row, so, must be some additional information missing that has to be accommodated that gives rise to duplicate results (e.g. the 5 "english" rows) quite likely to be status - so maybe status is what might group those lines together... Possibly HFXC.Comment is not actually the same value each time...

Or we need to introduce an artificial group  - and that could well be the ROW_NUMBER() function  e.g.

row_number() over (partition by  H.id, HFXC.LID order by HFXC.Comment)    -- the oder by should really take into account something more realistic - e.g. what ever identifier that gives the multi instance of (say) "english"

So, given the above (and not knowing the real data, reacting to just the test data provided) check out :

select [Shop ID], [Shop Name], 
       Max([Facilities in English]) as [Facilities in English],
       Max([Facilities in French]) as [Facilities in French],
       Max([Facilities in Spanish]) as [Facilities in Spanish],
       Max([Facilities in German]) as [Facilities in German]
FROM
(
   select   [Shop ID] = H.ID,
            [Shop Name] = H.Name,

            Row_Number() over (partition by H.ID, HFXC.LID order by HFXC.Comment) as RN,
            
            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
   AND    HFXC.LID in (1,2,3,4)   -- to get rid of the all NULL row.  
) s
Group by [Shop ID], [Shop Name], RN
Order by 1,2,3 desc, 4 desc, 5 desc, 6 desc

Open in new window


0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this can only be done by creation different subqueries for each "column", along with row numbers, and join on the row numbers...
if that "path to solution" does not help, I can help writing the sql...
0
 
ralmadaConnect With a Mentor Commented:
what about ?
select   [Shop ID] = H.ID,
            [Shop Name] = H.Name,
            
            max(CASE WHEN HFXC.LID = 1 THEN convert(NVARCHAR,HFXC.Comment) END) AS [Facilities in English],
            max(CASE WHEN HFXC.LID = 2 THEN convert(NVARCHAR,HFXC.Comment) END) AS [Facilities in French],
            max(CASE WHEN HFXC.LID = 3 THEN convert(NVARCHAR,HFXC.Comment) END) AS [Facilities in Spanish],
            max(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
group by H.ID,
         H.Name
   order by 3,4,5,6 desc

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
incercConnect With a Mentor Commented:
Hi,

Have you tried to use the PIVOT operator, in order to avoid the CASE statements ?

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
 
Mr_ShawAuthor Commented:
Just reading everything.. so for the delay.... :)
0
 
ralmadaConnect With a Mentor Commented:
Indeed you can use pivot as well. In this case you can try like this. And also the order by attached below could be an alternative to put all nulls last.
select 	ID,
	Name,
	[1] as [Facilities in English],
	[2] as [Facilities in French],
	[3] as [Facilities in Spanish],
	[4] as [Facilities in German]
from (
select 	H.ID,
	H.Name,
	HFXC.LID,
	convert(nvarchar(max), HFXC.Comment) as Comment
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
) o
PIVOT(max(Comment) for LID in([1],[2],[3],[4])) p
--this is optional
order by case when [1] is null then 1 else 0 end, 
	 case when [2] is null then 1 else 0 end, 
	 case when [3] is null then 1 else 0 end, 
	 case when [4] is null then 1 else 0 end

Open in new window

0
 
Mr_ShawAuthor Commented:
Thanks... Lots to think about ..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.