How to arrange the SQL output in order ?

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 ?
Varshini SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SimonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.