SQL Server 2008 - Find Locations for Artwork

Question

Consider the following table and data in a SQL Server 2008 database:

CREATE TABLE sponsorships 
(
    sponsorshipID INT NOT NULL PRIMARY KEY IDENTITY,
    sponsorshipLocationID INT NOT NULL,
    sponsorshipArtworkID INT NOT NULL
);

INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (1, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (1, 2);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (2, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (2, 2);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (3, 3);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (4, 3);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (5, 4);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (6, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (7, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) 
VALUES (7, 3);

SELECT *
FROM sponsorships s
ORDER BY s.sponsorshipLocationID, s.sponsorshipArtworkID

How can I produce the following output?

CREATE TABLE sponGroups 
(
    rank INT,
    sponsorshipID INT,
    sponsorshipLocationID INT,
    sponsorshipArtworkID INT
);

INSERT INTO sponGroups VALUES (1, 1, 1, 1);
INSERT INTO sponGroups VALUES (1, 2, 1, 2);
INSERT INTO sponGroups VALUES (1, 3, 2, 1);
INSERT INTO sponGroups VALUES (1, 4, 2, 2);
INSERT INTO sponGroups VALUES (2, 5, 3, 3);
INSERT INTO sponGroups VALUES (2, 6, 4, 3);
INSERT INTO sponGroups VALUES (3, 7, 5, 4);
INSERT INTO sponGroups VALUES (4, 8, 6, 1);
INSERT INTO sponGroups VALUES (5, 9, 7, 1);
INSERT INTO sponGroups VALUES (5, 10, 7, 3);

SELECT *
FROM sponGroups sg
ORDER BY sg.rank, sg.sponsorshipID, sg.sponsorshipLocationID, sg.sponsorshipArtworkID

Fiddle available, here.

Explanation

Artwork is displayed in various locations. Some locations have double-sided artwork installed (e.g. windows), and some single sided (e.g. walls). For example, the artwork at location 1 is double sided--it has sponsorshipArtworkIDs 1 and 2--and there is single-sided artwork at location 5 (sponsorshipArtworkID 4).

For printing and installation purposes, I need a query that yields each piece of artwork, be it one sided or two, and all the locations tied to that piece. (Reference the desired output in the fiddle linked above.) So, for example, I need to tell the printer:

Print the double-sided artwork (1,2) and install it at locations (1,2);
Print the single-sided artwork (3) and install it at locations (3,4);
Print the single-sided artwork (4) and install it at location (5);
Print the single-sided artwork (1) and install it at location (6);
Print the double-sided artwork (1,3) and install it at location (7).

Note that art is reused sometimes, so sponsorshipArtworkID 1 is used at both single- and double-sided locations.

I've attempted to use DENSE_RANK(), a recursive CTE, and set-wise divide to solve this problem, but haven't been able to as of yet. Thanks in advance for the help.

I've read the explanation, but still have no idea what the `rank` column should represent — Lamak, Mar 02 '17 at 21:40
@SqlZim Locations where double-sided artwork is installed have two pieces of art assigned. E.g. location 7 has two pieces of art assigned, so it is a double-sided location. — benbotto, Mar 02 '17 at 21:42
@Lamak The "rank" column is to illustrate my desired grouping; the bulleted list at the bottom of my explanation corresponds directly to the "rank" column. Basically I need to send each bullet/rank to the printer /installer sequentially as 5 separate requests in this case. — benbotto, Mar 02 '17 at 21:46
I understood that it represent the result that you want. I just don't get the explanation to obtain it — Lamak, Mar 02 '17 at 21:52
Sorry, @Lamak, but it's hard to put the problem into words. If you can tell me specifically which part of the explanation is difficult to understand, then I'll try to clarify that part of the question. — benbotto, Mar 02 '17 at 22:01

score 1 · Accepted Answer · answered Mar 03 '17 at 01:06

It may look a bit ugly, but idea is simple.

At first group by sponsorshipLocationID and build a comma-separated string with the list of associated sponsorshipArtworkID.

Then calculate dense rank based on this comma-separated string of Artwork IDs.

In the query below I use FOR XML to concatenate strings. It is not the only way to do it. There are good fast CLR functions written that do it.

I recommend to run the query below step-by-step, CTE-by-CTE and examine intermediate results to understand how it works.

Sample data

DECLARE @sponsorships TABLE
(
    sponsorshipID INT NOT NULL PRIMARY KEY IDENTITY,
    sponsorshipLocationID INT NOT NULL,
    sponsorshipArtworkID INT NOT NULL
);

INSERT INTO @sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES 
(1, 1),
(1, 2),
(2, 1),
(2, 2),
(3, 3),
(4, 3),
(5, 4),
(6, 1),
(7, 1),
(7, 3);

Query

WITH
CTE_Locations
AS
(
    SELECT
        sponsorshipLocationID
    FROM
        @sponsorships AS S
    GROUP BY
        sponsorshipLocationID
)
,CTE_Artworks
AS
(
    SELECT
        CTE_Locations.sponsorshipLocationID
        ,CA_Data.Artwork_Value
    FROM
        CTE_Locations
        CROSS APPLY
        (
            SELECT CAST(S.sponsorshipArtworkID AS varchar(10)) + ','
            FROM
                @sponsorships AS S
            WHERE
                S.sponsorshipLocationID = CTE_Locations.sponsorshipLocationID
            ORDER BY
                S.sponsorshipArtworkID
            FOR XML PATH(''), TYPE
        ) AS CA_XML(XML_Value)
        CROSS APPLY
        (
            SELECT CA_XML.XML_Value.value('.', 'NVARCHAR(MAX)')
        ) AS CA_Data(Artwork_Value)
)
,CTE_Rank
AS
(
    SELECT
        sponsorshipLocationID
        ,Artwork_Value
        ,DENSE_RANK() OVER (ORDER BY Artwork_Value) AS r
    FROM CTE_Artworks
)
SELECT
    CTE_Rank.r
    ,S.sponsorshipID
    ,CTE_Rank.sponsorshipLocationID
    ,S.sponsorshipArtworkID
FROM
    CTE_Rank
    INNER JOIN @sponsorships AS S 
        ON S.sponsorshipLocationID = CTE_Rank.sponsorshipLocationID
ORDER BY
    S.sponsorshipID
;

Result

+---+---------------+-----------------------+----------------------+
| r | sponsorshipID | sponsorshipLocationID | sponsorshipArtworkID |
+---+---------------+-----------------------+----------------------+
| 2 |             1 |                     1 |                    1 |
| 2 |             2 |                     1 |                    2 |
| 2 |             3 |                     2 |                    1 |
| 2 |             4 |                     2 |                    2 |
| 4 |             5 |                     3 |                    3 |
| 4 |             6 |                     4 |                    3 |
| 5 |             7 |                     5 |                    4 |
| 1 |             8 |                     6 |                    1 |
| 3 |             9 |                     7 |                    1 |
| 3 |            10 |                     7 |                    3 |
+---+---------------+-----------------------+----------------------+

The actual values of rank are not exactly the same as in your expected result, but they group rows correctly.

Thank you Vladimir, this does indeed accomplish the goal. It seems like this should be doable purely set-wise (without the concatenation, that is), but this is better than anything I've come up with. I appreciate the help. — benbotto, Mar 03 '17 at 02:35
@avejidah, there is a similar [question](http://stackoverflow.com/q/30680266/4116017) and solutions are similar to approach in my answer here. I showed there an approximate solution with `CHECKSUM_AGG`. — Vladimir Baranov, Mar 03 '17 at 03:42

SQL Server 2008 - Find Locations for Artwork

1 Answers1