Solved

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

Posted on 2010-11-16
7
297 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 70 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 150 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 80 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 150 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 200 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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