2

We previously used to store multi select data (enum) using pipe separated characters, as shown below like this - which made ordering on that enum data very simple.

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ |A|B|C|     ║
║ 2 ║ 2          ║ |B|D|       ║
║ 3 ║ 3          ║ |A|C|D|     ║
║ 4 ║ 4          ║ |A|C|       ║
║ 5 ║ 5          ║ |A|B|D|     ║
╚═══╩════════════╩═════════════╝

This above structure was easier to sort on the stored "Alphabets" values but search was expensive since we had to search using %|A|%.

Now we are using the following structure to store the same above data since searching the data becomes much easier:

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ A           ║
║ 2 ║ 1          ║ B           ║
║ 3 ║ 1          ║ C           ║
║ 4 ║ 2          ║ B           ║
║ 5 ║ 2          ║ D           ║
║ 6 ║ 3          ║ A           ║
║ 7 ║ 3          ║ C           ║
║ 8 ║ 3          ║ D           ║
║ 9 ║ 4          ║ A           ║
║ 10║ 4          ║ C           ║
║ 11║ 5          ║ A           ║
║ 12║ 5          ║ B           ║
║ 13║ 5          ║ D           ║
╚═══╩════════════╩═════════════╝

Searching becomes easy now but when I try to sort the data it gives nightmares. Can anyone give me a good idea to sort this above new data-structure using sql server so that I get the same sorted results as with the previous data-structure??

  • I think the best would be to keep both tables. You'll have to ensure - either through triggers or through insert/update/delete procedures - that the data in both tables are consistent). – ypercubeᵀᴹ May 8 '15 at 11:48
  • in the older scenario we just used the following to sort: select * from [tableX] order by [Alphabets] desc which returned the following: 2 |B|D| 3 |A|C|D| 4 |A|C 5 |A|B|D| 1 |A|B|C| – Deb May 8 '15 at 11:52
  • yes, duplicating data is a solution but a bad one (kind of not-acceptable) - which is exactly what I want to avoid, and, hence the question to the community..... :) – Deb May 8 '15 at 11:56
  • Why do you say it's bad? – ypercubeᵀᴹ May 8 '15 at 13:24
  • Have you tried using a recursive CTE to create the first structure from the scone on demand? – Pieter Geerkens May 9 '15 at 6:02
1

Below is an example of how you can return the ordered list using your new structure. What is missing from the database design is an ID table with one row per ID. This would then provide a one-to-many relationship in 3NF, and avoid the need to select the distinct ID values from the many side. You could use that parent table instead of the derived table in this example, which would be more efficient.

CREATE TABLE dbo.Alphabets(
    ID int NOT NULL
    ,Alphabets char(1) NOT NULL
    ,CONSTRAINT PK_Alphabets PRIMARY KEY (ID, Alphabets)
    );

INSERT INTO dbo.Alphabets VALUES
     (1, 'A')
    ,(1, 'B')
    ,(1, 'C')
    ,(2, 'B')
    ,(2, 'D')
    ,(3, 'A')
    ,(3, 'C')
    ,(3, 'D')
    ,(4, 'A')
    ,(4, 'C')
    ,(5, 'A')
    ,(5, 'B')
    ,(5, 'D');

SELECT  ID
        , ( SELECT    '|' + Alphabets
            FROM      dbo.Alphabets AS b
            WHERE     b.ID = a.ID
            ORDER BY  b.Alphabets
        FOR
            XML PATH('')
        ) + '|' AS Alphabets
FROM    ( SELECT DISTINCT
                    ID
            FROM      dbo.Alphabets
        ) AS a
ORDER BY Alphabets DESC;
|improve this answer|||||
  • Don't you think your proposed solution although elegant is not exactly different from the (original) existing data structure ??(another point being it would be hugely expensive for longer data). However I guess I can do some testing against existing data structure and your solution for searches...... :) – Deb May 8 '15 at 14:33
1

If you store the data in the concatenated way (your first table), then you have trouble searching. If you store the data in the second way you say it takes longer to sort (although I'm not clear on this)...

So have both. Every nonclustered index is a copy of the data, you could simply have your own, either as an indexed view or as a table you maintain with triggers.

I would suggest that if your data is currently in the first form, then you leave it like that and create an indexed view which joins to a table of numbers to separate it out. If there is some reason why an indexed view doesn't work, you could maintain a separate table with triggers, by deleting entries in the table in the deleted table and inserting entries from the inserted table.

Then you have the best of both worlds.

|improve this answer|||||
0

XML Path will slow down your query but that's what I can think of at the moment

create table #temp
(id int, data varchar(5))

insert into #temp values (1, 'A')
insert into #temp values (1, 'B')
insert into #temp values (1, 'C')
insert into #temp values (2, 'A')
insert into #temp values (2, 'B')


SELECT id, '|' + (SELECT t2.data + '|' from #temp t2 where t2.id = t1.id ORDER BY t2.Data FOR XML PATH(''))
from #temp t1
GROUP BY Id

DROP table #temp

Regards,

Maulin Thaker.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.