How to populate column with list from another table?

Question

Suppose I have three tables: STORES, STATES, and STORES_STATES.

STORES contains records of individual stores
STATES just contains a list of all 50 US states
STORES_STATES is a join table that contains pairs of stores and states to show which stores are available in which states

Consider the following tables (can't figure out how to format an ASCII table here):

What I need is a SELECT statement that will return each store in one column, and a list of states in another:

How do I combine the results of a subquery into a single column like this?

I would imagine it would be similar to this query:

SELECT
  STORE_NAME,
  (SELECT STATE_ABV FROM STORES_STATES WHERE STORES_STATES.STORE_ID = STORES.ID)
FROM STORES;

But this obviously fails since the subquery returns more than one result.

@Sami because it's `FOR XML` that does the tricks, not `STUFF` — MatBailie, Oct 29 '18 at 17:35
Tangential comment. I use the [ASCII Table Generator](https://ozh.github.io/ascii-tables/) site to format tabular data for SO. — Eric Brandt, Oct 29 '18 at 18:11

Yogesh Sharma · Accepted Answer · 2018-10-29T18:03:40.050

0

You can use APPLY :

SELECT s.store_name, STUFF(ss.state_abv, 1, 1, '') AS States
FROM stores s CROSS APPLY
     ( SELECT ', '+ss.state_abv
       FROM stores_state ss
       WHERE ss.store_id = s.id
       FOR XML PATH('')
     ) ss(state_abv);

edited Oct 29 '18 at 18:03

answered Oct 29 '18 at 17:35

Yogesh Sharma

49,870
5
26
52

This "works," but seems to return the STATES column with tags around each state. – Zephyr Oct 29 '18 at 17:45
@Zephyr. . . I forget to add `', '` in `APPLY`. – Yogesh Sharma Oct 29 '18 at 18:04

score 0 · Answer 2 · answered Oct 29 '18 at 17:42

your two options are either the STRING_AGG function in the newest versions of SQL Server, or using an XML concatenation technique as described in this answer:

How to concatenate text from multiple rows into a single text string in SQL server?

The XML method is messy-looking in your code and difficult to remember - I always have to look up the syntax - but it's actually quite fast.

Ilyes · Answer 3 · 2018-10-29T17:56:15.963

You can use STUFF() function along side with FOR XML PATH('') and join both tables on StoreID

CREATE TABLE Stores
(
  ID INT,
  StoreName VARCHAR(45)
);

CREATE TABLE States
(
  StoreID INT,
  StateABV VARCHAR(45)
);

INSERT INTO Stores VALUES
(1, 'Wolmart'), (2, 'Costco'), (3, 'Croegers');

INSERT INTO States VALUES
(1, 'NY'), 
(1, 'WY'),
(1, 'MI'),
(2, 'AL'),
(2, 'GA'),
(2, 'TX'),
(3, 'FL');

SELECT SR.StoreName,
       STUFF(
              (
                SELECT ',' + ST.StateABV
                FROM States ST
                WHERE ST.StoreID = SR.ID
                FOR XML PATH('')
              ), 1, 1, ''
           ) State
FROM Stores SR;

Returns:

+-----------+----------+
| StoreName |  State   |
+-----------+----------+
| Wolmart   | NY,WY,MI |
| Costco    | AL,GA,TX |
| Croegers  | FL       |
+-----------+----------+

How to populate column with list from another table?

3 Answers3