Solved

How to arrange the SQL output in  order ?

Posted on 2014-11-26
2
109 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
ID: 40467913
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:Simon
Simon earned 150 total points
ID: 40468146
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

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 …
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

11 Experts available now in Live!

Get 1:1 Help Now