?
Solved

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

Posted on 2010-11-16
7
Medium Priority
?
299 Views
Last Modified: 2012-05-10
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
Comment
Question by:Mr_Shaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 280 total points
ID: 34145876
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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 600 total points
ID: 34145927
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
 
LVL 4

Assisted Solution

by:incerc
incerc earned 320 total points
ID: 34145957
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mr_Shaw
ID: 34146012
Just reading everything.. so for the delay.... :)
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 600 total points
ID: 34146041
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 800 total points
ID: 34146885
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
 

Author Closing Comment

by:Mr_Shaw
ID: 34153810
Thanks... Lots to think about ..
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question