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 wantsApples
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 wantsApples
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 wantsApples
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