Solved

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

Posted on 2010-11-16
7
290 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
7 Comments
 
LVL 142

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now