Solved

How to arrange the SQL output in  order ?

Posted on 2014-11-26
2
111 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 25

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

Technology Partners: 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!

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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