Solved

How to arrange the SQL output in  order ?

Posted on 2014-11-26
2
100 Views
Last Modified: 2014-11-27
I have the following table

Name                Qualification
---------------------------------------------
Steve                  IT|MBA|BA
John                   MS|IT|MBA|BA
Chris                  IT|BA
Mike                   MS|MBA

I need to order the output in the following way

Name                Qualification
Steve                  BA|MBA|IT
John                   BA|MBA|IT|MS
Chris                 BA|IT
Mike                  MBA|MS



Qualification needs to be in this order >   BA,MBA,IT and MS

How to achieve this in SQL query ?
0
Comment
Question by:Varshini S
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 350 total points
Comment Utility
First of all, you need to think of ways to normalise your database. I recommend that you create a table called qualification where you would list all the individual qualifications along with a QualificationID key. The table would have:
Table: Qualification
QualificationID(key)    Qualification CustomOrder
1                        IT            30
2                        BA            10
3                        MBA           20

Open in new window

As you can see, in the table above I have used the integer values 10, 20, 30 as a CustomOrder. I used them with gaps deliberately. If you add a qualification later on that needs to go between 10 and 20 you would assign a CustomOrder of 15 without the need to re-shuffle the whole table
Then, you would have a table called PersonQualification. It will have a composite key consisting of two columns: PersonID and QualificationID. It may have some additional columns if you wish, e.g. DateAssessed:
Table: PersonQualification
PersonID  QualificationID  DateAssessed
1          1              2014-10-01
1          2              2012-10-01
2          1              2011-08-01

Open in new window

That was just a side note.
Now, to your problem. You need a function that will split the qualification list to values and then you would use a FOR XML PATH query to combine the values back to the string. There are quite a few functions that do this. I am using the first one found by Google from here:
    CREATE FUNCTION dbo.SplitStrings_XML
    (
       @List       NVARCHAR(MAX),
       @Delimiter  NVARCHAR(255)
    )
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
       RETURN 
       (  
          SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
          FROM 
          ( 
            SELECT x = CONVERT(XML, '<i>' 
              + REPLACE(@List, @Delimiter, '</i><i>') 
              + '</i>').query('.')
          ) AS a CROSS APPLY x.nodes('i') AS y(i)
       );

Open in new window

Now, to split the string you would write this query:
select s.Name, t.Item
from Table1 s
  CROSS APPLY dbo.SplitStrings_XML(s.Qualification, '|') AS t
Order by 1,2;

Open in new window

And finally, all you need to do is to combine the string back to the delimited list:
SELECT      P.Name,
            STUFF((    SELECT ',' + Q.Item AS [text()]
                        FROM (select s.Name, t.Item
                              from Table1 s
                              CROSS APPLY dbo.SplitStrings_XML(s.Qualification, '|') AS t) Q
                        WHERE
                        Q.Name = P.Name
                        Order BY Q.Item
                        FOR XML PATH('')
                        ), 1, 1, '' )
            AS [Qualifications]
FROM  Table1 P;

Open in new window

Result:
|  NAME | QUALIFICATIONS |
|-------|----------------|
| Steve |      BA,IT,MBA |
|  John |   BA,IT,MBA,MS |
| Chris |          BA,IT |
|  Mike |         MBA,MS |

Open in new window

Now, to use the custom order you need to adjust the ORDER BY clause a little:
SELECT      P.Name,
            STUFF((    SELECT ',' + Q.Item AS [text()]
                        FROM (select s.Name, t.Item
                              from Table1 s
                              CROSS APPLY dbo.SplitStrings_XML(s.Qualification, '|') AS t) Q
                        WHERE
                        Q.Name = P.Name
                        Order BY 
                        CASE Q.Item 
                         WHEN 'BA' THEN 1
                         WHEN 'MBA' THEN 2
                         WHEN 'IT' THEN 3
                         WHEN 'MS' THEN 4
                        ELSE 99 END
                        FOR XML PATH('')
                        ), 1, 1, '' )
            AS [Qualifications]
FROM  Table1 P;

Open in new window

Results:
|  NAME | QUALIFICATIONS |
|-------|----------------|
| Steve |      BA,MBA,IT |
|  John |   BA,MBA,IT,MS |
| Chris |          BA,IT |
|  Mike |         MBA,MS |

Open in new window

0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 150 total points
Comment Utility
If you're stuck with the input table that you've shown in the example, here is another approach. Not pretty but works on your existing data structure for your stated scenario.

select name,
stuff(
 case when ' ' + qualification like '%[^M]BA%' then 'BA|' else '' END
+ case when qualification like  '%MBA%' then 'MBA|' else '' END
+ case when qualification like  '%IT%' then 'IT|' else '' END
+ case when qualification like  '%MS%' then 'MS|' else '' END,
  len(
    case when ' ' + qualification like '%[^M]BA%' then 'BA|' else '' END
+ case when qualification like  '%MBA%' then 'MBA|' else '' END
+ case when qualification like  '%IT%' then 'IT|' else '' END
+ case when qualification like  '%MS%' then 'MS|' else '' END),1,'')
as qualification
from testtable

Open in new window


If it wasn't necessary to trim off the last pipe char the statement would be much shorter.

This approach would soon get unwieldy if you extended the range of possible qualifications, but works OK for a limited range, simply by searching for each of the patterns in turn to build up the desired result string.

Thanks to http://sqlfiddle.com for providing an online version of MSSQL2008 to test this on :) You can try my code with a sample table there for as long as it persists.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

8 Experts available now in Live!

Get 1:1 Help Now