Get a record and SIMILAR records from SQL in same result row

Question

This is tricky and I'm stuck. I need to receive SKU details from a table that has inventory records, with added columns that have another SKU that's a match based on same size, same brand etc.

So the query I'm running is this:

SELECT h.orders_header_id,
       h.order_number,
       CONVERT(VARCHAR,h.order_date, 102) AS order_date,
       h.first_sku,
       h.first_title,
       i.size,
       i.brand,
       i.pic,
       i.channel,
       h.c_email,
       h.delivery_status
FROM orders_header h
LEFT JOIN
  (SELECT sku,
          SIZE,
          brand,
          imageserver + sku + 'A.JPG' AS pic,
          channel
   FROM inv_ALL) i ON h.first_sku = i.sku
WHERE h.delivery_status LIKE '%|Delivered|%'
  AND h.delivery_status NOT LIKE '%Arrival in Destination Country%'
  AND h.delivery_status LIKE (CONVERT(VARCHAR, GETDATE(), 112) +'%')
  AND i.pic IS NOT NULL

What I need is to have these columns:

S1,S1title,S1pic,S2,S2title,S2pic,S3,S3title,S3pic

Where S1 would be a matching SKU pulled from inv_ALL table that has same size and brand as h.first_sku and non-zero i.availablequantity. S1title would be it's title and S1pic would be it's picture (as above). Same for S2 and S3. And S1/S2/S3 have to be SKUs other than first_sku.

These are supposed to be 'Top Picks' to be shown to customers along with their purchase as a recommendation.

I have been trying since yesterday, but can't think of a way to make it pull these records in the same row as added columns.

(Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Here's a screenshot of what the results look like right now:

It looks like you may be looking for [pivot queries](https://stackoverflow.com/questions/15931607). I strongly suggest populating a temporary table (or table variable) with filtered data after sanitizing first before pivoting though. — Elaskanator, Feb 05 '19 at 21:29
Sure, here's a screenshot of what the results look like right now: https://i.imgur.com/4C3IFLe.png — erohtar, Feb 05 '19 at 21:30
If I'm using Pivot, how do I provide WHERE parameters to it when those values depend on the results of the query? Like if the first row result has 'Betsy & Adam' as brand and '6P', then in the new columns I need SKUs that have the same brand and size. — erohtar, Feb 05 '19 at 21:33
Use a temp table, otherwise use subqueries (e.g. `SELECT * FROM (SELECT Col1, Col2, ...FROM #X WHERE ...) AS u PIVOT(MAX(Value) FOR Col2 IN ([label1], ...)) AS p`) — Elaskanator, Feb 05 '19 at 21:58
@Elaskanator please see my last comment. I'm not sure how to tackle that problem. If you could give me some pseudo code (doesn't need to be fully functional), then I'd certainly try and see if it works for me. — erohtar, Feb 05 '19 at 22:12

score 0 · Answer 1 · answered Feb 06 '19 at 05:20

So after a long struggle, I've got it to work. The result is exactly how I want, but I'm pretty sure that it's far from resource-efficient.

I've created a temporary table variable and put the items that have matching attributes there. But to pick 3 matching items, I'm using JOIN thrice on the same table (which I bet can be replaced with a better approach) because I want to pick different item in each column based on rank.

DECLARE @skus TABLE
(rk INT, sku VARCHAR(50), brandsize VARCHAR(300), title VARCHAR(500), pic VARCHAR(500))
INSERT INTO @skus
    SELECT ROW_NUMBER() OVER(PARTITION BY channel + '_' + brand + '_' + size ORDER BY sku) AS rk, sku, channel + '_' + brand + '_' + size, title, imageserver + sku + 'A.JPG'
    FROM inv_ALL
    WHERE availablequantity > 0
    AND channel + '_' + brand + '_' + size IS NOT NULL
    ORDER BY channel + '_' + brand + '_' + size

SELECT h.orders_header_id, h.order_number, CONVERT(VARCHAR,h.order_date,102) AS order_date, h.site, h.first_sku, h.first_title, i.size, i.brand, i.pic, i.channel, h.c_email, h.c_name, h.c_address1, h.c_address2, h.c_address3, h.c_city, h.c_state, h.c_zip, h.c_country, h.c_phone, h.delivery_status, s1.t1, s1.p1, s2.t2, s2.p2, s3.t3, s3.p3
FROM chanadv.dbo.orders_header h
LEFT JOIN (SELECT sku, size, brand, imageserver + sku + 'A.JPG' AS pic, channel FROM casql.dbo.inv_ALL) i ON h.first_sku = i.sku
LEFT JOIN (SELECT brandsize, title AS t1, pic AS p1 FROM @skus WHERE rk = 1) s1 ON i.channel + '_' + i.brand + '_' + i.size = s1.brandsize
LEFT JOIN (SELECT brandsize, title AS t2, pic AS p2 FROM @skus WHERE rk = 2) s2 ON i.channel + '_' + i.brand + '_' + i.size = s2.brandsize
LEFT JOIN (SELECT brandsize, title AS t3, pic AS p3 FROM @skus WHERE rk = 3) s3 ON i.channel + '_' + i.brand + '_' + i.size = s3.brandsize
WHERE h.delivery_status LIKE '%|Delivered|%'
AND h.delivery_status NOT LIKE '%Arrival in Destination Country%'
AND h.delivery_status LIKE (CONVERT(VARCHAR, GETDATE(), 112) +'%')
AND h.c_country = ''
AND i.pic IS NOT NULL

Get a record and SIMILAR records from SQL in same result row

1 Answers1