SQL Server Grouping question (I think)

Databases
1

I have a SQL Server database that contains an inventory of highway noise barriers. I constructed it like this:

Tables:
barriers - this is the list of barriers, with ID numbers and all relevant info about each one
materials - this is a list of all the possible types of material that the barriers can be made from
states - this is a list of states
barrier_mat - this joins the barrier ID to the material ID. Barriers can consist of more than one material.

I’ve used this database to present the data in several ways, including a search page where you can pick a state and a year, etc and look at the info. But one of the ways I’ve been asked to present it has me stumped.

I’ve been asked to present a list of all the different material combos, and the total area that they occupy. Doing this for single material barriers is easy. I just go through the materials table and do a SUM() on the area column. But doing it for combinations of materials is harder.

For example:
Combination, Berm, Block, Wood, Other: 34,000 sq ft
Combination, Berm, Cast In Place Concrete, Precast Concrete: 64,000 sq ft
Combination, Fiberglass, Metal, Precast Concrete: 36,000 sq ft

Each of those materials in those lists is a single row in the materials column. I’m not sure how to get the query to give me groups like that. Is there a straightforward way to do this?

2

I’m thinking the best approach would be a pivot table, @r937 probably has a better approach.

Here’s a thread which is very similar to what you’re talking about, and one I’ve used for a reference several times. The biggest thing would be to make sure that the materials are returned in the same order to ensure you get matches.

3

is your objective to sum up each barrier only under the specific sets of materials that it contains?

if so, that’s more than a pivot :wink:

straightforward? i think so

where’s this square footage column of which you speak?

oh, and how do you want the materials in the lists sorted? lexically?

could you perhaps do a SHOW CREATE TABLE for each table?

4

The area column is in the barriers table. The materials are all in their own table, and joined many-to-many by a third table (barrier_mat). I want to order by total area, desc.

I’ll post a show create table tomorrow. I had to bail out of work early because I wanted to avoid the lines at the polls in my state.

5

ok, take your time, i will be here

the first line of my previous post, could you comment on that please?

6

Sure! and thanks for taking the time to help me with this.

This page is supposed to show the barrier types by combined material, and the sum of the area for all the barriers that have that combo of materials. So, this:

Precast, berm, fiberglass is one type of material combo, and I want the total area for all the barriers that are made up of that exact combo.

Precast, berm (without fiberglass) is another combo, and I need to add those up separately.

I basically need area totals for all the material combos.

7

hey, you know what? i just realized you said SQL Server

in MySQL, i would make use of the GROUP_CONCAT function, and the query is really easy

but in SQL Server, i think you have to use FOR XML, which i’ve seen people say is what you need to collapse a column ov values into a single concatenated string

so since i’ve never tried FOR XML, i can’t help you

i mean, i ~could~ go and find SQL Server Express and download it and start testing, but i’m not gonna, sorry

8

Sorry, I’ve been home sick all day :frowning:

I’ll do a Google for “FOR XML” and see what I can come up with.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.