?
Solved

How to arrange the SQL output in  order ?

Posted on 2014-11-26
2
Medium Priority
?
118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 1400 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 600 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 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