[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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

0
Mr_Shaw
Asked:
Mr_Shaw
5 Solutions
 
Guy Hengel [angelIII / a3]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
 
ralmadaCommented:
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
 
incercCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Mr_ShawAuthor Commented:
Just reading everything.. so for the delay.... :)
0
 
ralmadaCommented:
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
 
Mark WillsTopic 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
 
Mr_ShawAuthor Commented:
Thanks... Lots to think about ..
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now