SQL Server: How to find the result with 2 criteria: one equals certain column; the other doesn't equal another column

Question

I got a question as shown in pic: SKU is unique, its name can get different sizes.

I want to use SQL Server to display its other size (with same NAME).

How can I achieve it?

Many thanks in advance.

Answer 1

For dynamic query

IF OBJECT_ID(N'tempdb..#Tbl', N'U') IS NOT NULL DROP TABLE #Tbl;
IF OBJECT_ID(N'tempdb..#TempTpl', N'U') IS NOT NULL DROP TABLE #TempTpl;

CREATE TABLE #Tbl (SKU NVARCHAR(50), Name NVARCHAR(50), Size NVARCHAR(5))
INSERT INTO #Tbl
VALUES ('DRESS-M', 'DRESS', 'M'  ),
       ('DRESS-S', 'DRESS', 'S'  ),
       ('BLOUSE-L', 'BLOUSE', 'L'),
       ('BLOUSE-XL', 'BLOUSE', 'XL'),
       ('BLOUSE-S', 'BLOUSE', 'S'),
       ('SKIRT-S', 'SKIRT', 'S'  ),
       ('SKIRT-M', 'SKIRT', 'M'  ),
       ('SKIRT-L', 'SKIRT', 'L'  ),
       ('SKIRT-XL', 'SKIRT', 'XL'),
       ('SKIRT-XS', 'SKIRT', 'XS'),
       ('SKIRT-XS', 'SKIRT', 'XXL')

CREATE TABLE #TempTpl (SKU NVARCHAR(50), Name NVARCHAR(50), Size NVARCHAR(5), RowNumber INT)
INSERT INTO #TempTpl
SELECT 
    SKU,
    Name,
    Size,       
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Size) AS RowNumber
FROM 
    #Tbl    

DECLARE @Sizes TABLE (Size NVARCHAR(5))

INSERT INTO @Sizes
SELECT DISTINCT Size FROM #Tbl
WHERE
    Name = (                
                SELECT TOP 1
                    A.Name
                FROM
                (
                    SELECT Name, COUNT(1) AS CountOfSize FROM #Tbl
                    GROUP BY Name
                ) A
                ORDER BY A.CountOfSize DESC
            )

DECLARE @TmpSelect NVARCHAR(MAX)
SELECT 
     @TmpSelect = COALESCE(@TmpSelect + ', ','') + '(SELECT TOP 1 Size FROM #TempTpl AS IT WHERE IT.Name = T.Name AND IT.RowNumber <> T.RowNumber AND IT.RowNumber = ' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NVARCHAR(5)) +') AS Size' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NVARCHAR(5))
FROM @Sizes


DECLARE @Query NVARCHAR(MAX)
SET @Query ='
            SELECT
                T.SKU,
                T.Name, 
                T.Size,' +
                    @TmpSelect          
                +'
            FROM
                #TempTpl T
            ORDER BY 
                T.Name,
                Size'
--SELECT @Query
EXEC  sp_executesql @Query

Output

SKU             Name        Size  Size1 Size2 Size3 Size4 Size5 Size6
--------------- ----------- ----- ----- ----- ----- ----- ----- -----
BLOUSE-L        BLOUSE      L     NULL  S     XL    NULL  NULL  NULL
BLOUSE-S        BLOUSE      S     L     NULL  XL    NULL  NULL  NULL
BLOUSE-XL       BLOUSE      XL    L     S     NULL  NULL  NULL  NULL
DRESS-M         DRESS       M     NULL  S     NULL  NULL  NULL  NULL
DRESS-S         DRESS       S     M     NULL  NULL  NULL  NULL  NULL
SKIRT-L         SKIRT       L     NULL  M     S     XL    XS    XXL
SKIRT-M         SKIRT       M     L     NULL  S     XL    XS    XXL
SKIRT-S         SKIRT       S     L     M     NULL  XL    XS    XXL
SKIRT-XL        SKIRT       XL    L     M     S     NULL  XS    XXL
SKIRT-XS        SKIRT       XS    L     M     S     XL    NULL  XXL
SKIRT-XXL       SKIRT       XXL   L     M     S     XL    XS    NULL

Answer 2

Here is a solution which will work well for three sizes. The inner group by query matches a given size with the maximum (lexigraphically) other size. The second join then attaches the third size. In cases where there be only one or two sizes, the placeholder NA is used in the result set.

I have tested this query on SQL Server directly and it appears to be working, at least for the edge cases I tested.

SELECT t1.SKU,
       t1.NAME,
       t1.SIZE AS SIZE1,
       COALESCE(t1.OtherSize, 'NA') AS SIZE2,
       COALESCE(t2.SIZE, 'NA') AS SIZE3
FROM
(
    SELECT t1.SKU, t1.NAME, t1.SIZE, MAX(t2.SIZE) AS OtherSize
    FROM yourTable t1
    LEFT JOIN yourTable t2
        ON t1.NAME = t2.NAME AND t1.SIZE <> t2.SIZE
    GROUP BY t1.SKU, t1.NAME, t1.SIZE        
) t1
LEFT JOIN yourTable t2
    ON t1.NAME = t2.NAME AND
       t2.SIZE <> t1.SIZE AND
       t2.SIZE <> t1.OtherSize

Here is the output I got: