December 16, 2014 at 10:25 am
Hello,
I am looking for a way (if possible) to create a query that will produce an alphabetical list with letter header.
For example, it would look like this:
A
apple
Artichoke
Apricot
Asparagus
B
Banana
broccoli
blueberry
C
carrots
cucumber
cabbage
Is there a way to create a query to do this?
December 16, 2014 at 10:31 am
Yes, UNION in "A", "B", "C", ..., "Z" to the source table and then sort it, something like this:
SELECT name
FROM table_name
UNION ALL
SELECT v.letter
FROM (VALUES('A'),('B'),('C')) AS v(letter)
ORDER BY name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 16, 2014 at 10:40 am
Thank you!!!
December 17, 2014 at 9:21 am
Hi, Is there a way to omit a letter from the list if there is no value?
Using the earlier example, if I do not have a fruit or veggie that starts with "Q", I do not want the "Q" to appear on the report.
December 17, 2014 at 9:26 am
One way (not necessarily the most efficient) is to join v back to table_name on v.letter = left(table_name.name) before you do the UNION.
John
December 17, 2014 at 9:32 am
SELECT name
FROM table_name
UNION ALL
SELECT DISTINCT LEFT(name, 1) AS letter
FROM table_name
ORDER BY name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2014 at 9:41 am
And thanks again, thank you both!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply