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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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