group query output

Question

group query output

SQL_NuB

Hall of Fame

Points: 3666
More actions
November 2, 2010 at 8:43 am

#142322

Is this something that SQL can do, or should I just code for it within the app?
I'm looking to do this. I'm looking to output my data as so.
I'd like to have this
Dog
Doberman
Bulldog
Poodle
Cat
Persian
Black
Calaco
Reptiles
Lizard
Snake
but comes out like
dog doberman
dog Bulldog
dog Poodle
cat black
cat calaco
cat persian
reptiles snake
retiles lizard
and so on, is this something that I can have a query do so the data is outputted like above, or should I just have the web app code for it?

Viewing 3 posts - 1 through 3 (of 3 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

Sounds simple... if the database is designed right.

I assume that you have a table of species (cat, dog, reptile), and a table of types (bulldog, doberman) that point to the appropriate species.

declare @Species TABLE (

SpeciesID INT IDENTITY PRIMARY KEY CLUSTERED,

Name varchar(50));

declare @SpeciesTypes TABLE (

SpeciesID INT,

Name varchar(50),

PRIMARY KEY (SpeciesID, Name));

insert into @Species values ('Cat');

insert into @Species values ('Dog');

insert into @Species values ('Reptile');

insert into @SpeciesTypes values (1, 'Persian');

insert into @SpeciesTypes values (1, 'Black');

insert into @SpeciesTypes values (1, 'Calaco');

insert into @SpeciesTypes values (2, 'Bulldog');

insert into @SpeciesTypes values (2, 'Doberman');

insert into @SpeciesTypes values (2, 'Poodle');

insert into @SpeciesTypes values (3, 'Lizard');

insert into @SpeciesTypes values (3, 'Snake');

From here, it's just a simple select statement:

SELECT s.Name,

st.Name

FROM @Species s

JOIN @SpeciesTypes st

ON s.SpeciesID = st.SpeciesID;

Does this answer your question? If not, please look at the first two articles in my signature, and try to post something with table DDL, sample data via INSERT statement(s), and expected results based on the sample data so that we know what you're really talking about.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 2

I got it, I had to make a query change and then I grouped everything via the code within the app.

Though for future, could SQL give me output like this or should I just keep doing it within the code of the app?