A-Z list

  • REVISED Question:

    I want to return all data (varchar) from a column in a table and have it sorted alphabetically a-z as such:

    SELECT Fruit FROM FruitTable ORDER BY Fruit ASC

    Apples

    Apricots

    Bananas

    Berries

    Clementime

    ..and so on

    But I also want to have results additionally formatted as such:

    A FRUITS

    Apples

    Apricots

    B FRUITS

    Bananas

    Berries

    C FRUITS

    Clementime

    ..and so on to Z FRUITS

    Hopefully this is clearer

    Thanks

    ******************************

    Want to perform a simple SELECT * query and ORDER BY [ColumnX] ASC.

    A simple single column query of, say, names.

    Would like to organize the data returned as such:

    A

    Apples

    Appricots

    B

    Banana

    Blue Berries

    C

    Clementime

    ....

    .....

    Not able to format client side.

  • something like this

    with x (id,letter)

    as

    (

    select 65 as id, CHAR(65) as letter

    union all

    select ID+1,CHAR(id+1) as letter from x

    where ID<90

    )

    select letter as name from x

    union all

    select name from sysobjects

    order by name asc

    option (Maxrecursion 25);

    MVDBA

  • barryocteau (6/1/2012)


    Want to perform a simple SELECT * query and ORDER BY [ColumnX] ASC.

    A simple single column query of, say, names.

    Would like to organize the data returned as such:

    A

    Apples

    Appricots

    B

    Banana

    Blue Berries

    C

    Clementime

    ....

    .....

    Not able to format client side.

    Not really sure what you are asking for here. To provide you with a better answer, please read the first article I reference below in my signature block. If you follow the instructions in that article regarding what to post and how to post it, we can provide you with much better answers.

  • barryocteau (6/1/2012)


    Want to perform a simple SELECT * query and ORDER BY [ColumnX] ASC.

    A simple single column query of, say, names.

    Would like to organize the data returned as such:

    A

    Apples

    Appricots

    B

    Banana

    Blue Berries

    C

    Clementime

    ....

    .....

    Not able to format client side.

    Include more detail as per this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D. This will help the unpaid volunteers of this site to help you answer your question.

    Are you asking about order by?

    SELECT *

    FROM (VALUES ('Banana'),('C'),('Clementime'),

    ('Apples'),('Appricots'),('A'),

    ('Blue Berries'),('B')

    )a(columnX);

    Returns: -

    columnX

    ------------

    Banana

    C

    Clementime

    Apples

    Appricots

    A

    Blue Berries

    B

    SELECT *

    FROM (VALUES ('Banana'),('C'),('Clementime'),

    ('Apples'),('Appricots'),('A'),

    ('Blue Berries'),('B')

    )a(columnX)

    ORDER BY columnX ASC;

    Returns: -

    columnX

    ------------

    A

    Apples

    Appricots

    B

    Banana

    Blue Berries

    C

    Clementime


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • im guessing that the OP wants

    Apples

    bannanas

    Chocolate

    Coffee

    turning into

    A

    Apples

    B

    Bannanas

    C

    Chocolate

    Coffee

    MVDBA

  • michael vessey (6/1/2012)


    im guessing that the OP wants

    Apples

    bannanas

    Chocolate

    Coffee

    turning into

    A

    Apples

    B

    Bannanas

    C

    Chocolate

    Coffee

    I really need to get one of those crystal balls that you guys all seem to have :w00t:

    I guess I'd do it like this (if I had to)

    SELECT *

    FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1)

    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

    (10),(11),(12),(13),(14),(15),(16),

    (17),(18),(19),(20),(21),(22),(23),(24))a(N)

    UNION ALL

    SELECT columnX

    FROM (VALUES ('Banana'),('Clementime'),

    ('Apples'),('Appricots'),

    ('Blue Berries')

    )a(columnX) --this would be the table, but since no DDL was supplied. . .

    )a(columnX)

    ORDER BY a.columnX ASC;

    Failed to know my alphabet there. . . :blush:


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • All a bit unclear....

    WITH Data AS (

    SELECT name

    FROM (VALUES ('Banana'),('Clementime'),

    ('Apples'),('Appricots'),

    ('Blue Berries')

    )a(name) )

    SELECT name

    FROM data

    UNION ALL

    SELECT DISTINCT LEFT(name,1)

    FROM data

    ORDER BY name;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Cadavre (6/1/2012)


    michael vessey (6/1/2012)


    im guessing that the OP wants

    Apples

    bannanas

    Chocolate

    Coffee

    turning into

    A

    Apples

    B

    Bannanas

    C

    Chocolate

    Coffee

    I really need to get one of those crystal balls that you guys all seem to have :w00t:

    I guess I'd do it like this (if I had to)

    SELECT *

    FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1)

    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

    (10),(11),(12),(13),(14),(15),(16),

    (17),(18),(19),(20),(21),(22),(23),(24))a(N)

    UNION ALL

    SELECT columnX

    FROM (VALUES ('Banana'),('Clementime'),

    ('Apples'),('Appricots'),

    ('Blue Berries')

    )a(columnX) --this would be the table, but since no DDL was supplied. . .

    )a(columnX)

    ORDER BY a.columnX ASC;

    Aren't you short a couple of values in your values clause? I always thought we had 26 letters in the alphabet.

  • Lynn Pettis (6/1/2012)


    Cadavre (6/1/2012)


    michael vessey (6/1/2012)


    im guessing that the OP wants

    Apples

    bannanas

    Chocolate

    Coffee

    turning into

    A

    Apples

    B

    Bannanas

    C

    Chocolate

    Coffee

    I really need to get one of those crystal balls that you guys all seem to have :w00t:

    I guess I'd do it like this (if I had to)

    SELECT *

    FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1)

    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

    (10),(11),(12),(13),(14),(15),(16),

    (17),(18),(19),(20),(21),(22),(23),(24))a(N)

    UNION ALL

    SELECT columnX

    FROM (VALUES ('Banana'),('Clementime'),

    ('Apples'),('Appricots'),

    ('Blue Berries')

    )a(columnX) --this would be the table, but since no DDL was supplied. . .

    )a(columnX)

    ORDER BY a.columnX ASC;

    Aren't you short a couple of values in your values clause? I always thought we had 26 letters in the alphabet.

    LOL! :blush:

    OK, that was a bit of an all around fail :w00t:

    SELECT *

    FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1)

    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

    (10),(11),(12),(13),(14),(15),(16),

    (17),(18),(19),(20),(21),(22),(23),

    (24),(25),(26))a(N)

    UNION ALL

    SELECT columnX

    FROM (VALUES ('Banana'),('Clementime'),

    ('Apples'),('Appricots'),

    ('Blue Berries')

    )a(columnX) --this would be the table, but since no DDL was supplied. . .

    )a(columnX)

    ORDER BY a.columnX ASC;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • i also had to count out loud the alphabet then decided ASCII('a') and ASCII('z') gave me the numbers i needed

    MVDBA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply