Is there any way to solve this? I am practicing SQL and I don't know how to do this.

Image table

----------------------------
| prdctCode |  imgPath     |
| P0003     |  P0003-1.jpg |
| P0003     |  P0003-2.jpg |
| P0003     |  P0003-3.jpg |
| P0004     |  P0004-1.jpg |
| P0004     |  P0004-2.jpg |
| P0004     |  P0004-3.jpg |
----------------------------

Product table

-------------------------
| prdctCode | prdctName |
| P0003     | Hand Bag  |
| P0004     | Pencil    |
-------------------------

What I get

---------------------------------------
| prdctCode | prdctName | imgPath     |
| P0003     | Hand Bag  | P0003-1.jpg |
| P0003     | Hand Bag  | P0003-2.jpg |
| P0003     | Hand Bag  | P0003-3.jpg |
| P0004     | Pencil    | P0004-1.jpg |
| P0004     | Pencil    | P0004-2.jpg |
| P0004     | Pencil    | P0004-3.jpg |
---------------------------------------

Expected output

--------------------------------------------------------------------
| prdctCode | prdctName | imgPath1     |  imgPath2   |  imgPath3   |
| P0003     | Hand Bag  | P0003-1.jpg  | P0003-2.jpg | P0003-3.jpg |
| P0004     | Pencil    | P0004-1.jpg  | P0004-2.jpg | P0004-3.jpg |
--------------------------------------------------------------------

This is my code

 select prdTbl.prdctCode, prdTbl.prdctName, imgTbl.imgPath  
 from [product_tbl]prdTbl left join [image_tbl]imgTbl 
   on prdTbl.prdctCode = imgTbl.prdctCode

I'm not sure if this is possible.

CodePudding user response：

If you know for sure that for a product will always be maximum 4 images and that the image is always ending with .jpg, maybe this select can help:

select prdTbl.prdctCode, prdTbl.prdctName, imgTbl2.imgPath as Path1, imgTbl2.imgPath as Path2, imgTbl3.imgPath as Path3, imgTbl4.imgPath as Path4
from [product_tbl]prdTbl 
left join [image_tbl]imgTbl1 on prdTbl.prdctCode = imgTbl1.prdctCode and imgTbl1.imgPath like '%-1.jpg'
left join [image_tbl]imgTbl2 on prdTbl.prdctCode = imgTbl2.prdctCode and imgTb12.imgPath like '%-2.jpg'
left join [image_tbl]imgTbl3 on prdTbl.prdctCode = imgTbl3.prdctCode and imgTbl3.imgPath like '%-3.jpg'
left join [image_tbl]imgTbl4 on prdTbl.prdctCode = imgTbl4.prdctCode and imgTbl4.imgPath like '%-4.jpg'

CodePudding user response：

Please consider looking at that script :

CREATE TABLE #TempAppointmentTable (
    UniqueID VARCHAR(MAX),
    VAL nvarchar(500)
);
    
INSERT INTO #TempAppointmentTable
    ([UniqueID], [VAL])
VALUES
    ('P0003', 'P0003-1.jpg'),
    ('P0003', 'P0003-2.jpg'),
    ('P0003', 'P0003-3.jpg'),
    ('P0004', 'P0004-1.jpg'),
    ('P0004', 'P0004-2.jpg')
;

DECLARE @list_id TABLE (idx INT IDENTITY, id VARCHAR(MAX))
DECLARE @nb_rows INT
DECLARE @nb_cols INT
DECLARE @i INT = 0
DECLARE @j INT = 0
DECLARE @cur_id VARCHAR(MAX)
DECLARE @cur_nb_rows INT
DECLARE @sql VARCHAR(MAX)

INSERT INTO @list_id(id) SELECT [UniqueID] FROM #TempAppointmentTable GROUP BY [UniqueID] ORDER BY COUNT(*) DESC;
SELECT @nb_rows = COUNT(*) FROM @list_id;
SELECT @nb_cols = MAX(val) FROM (SELECT COUNT(*) FROM #TempAppointmentTable GROUP BY [UniqueID])t(val);

SET @sql = '
WITH TMP ([Row], [UniqueID], [VAL]) AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY [UniqueID] ORDER BY UniqueID), [UniqueID], [VAL] from #TempAppointmentTable
)'

WHILE @i < @nb_rows
BEGIN
    SELECT @cur_id = id FROM @list_id WHERE idx = @i 1
    SELECT @cur_nb_rows = COUNT(*) FROM #TempAppointmentTable WHERE [UniqueID] = @cur_id
    IF @i != 0 
        SET @sql = @sql   '
UNION'
    SET @sql = @sql   '
SELECT TMP'   CONVERT(VARCHAR,@i)   '0.[UniqueID], TMP'   CONVERT(VARCHAR,@i)   '0.[VAL]'
    WHILE @j 1 < @nb_cols
    BEGIN
        SET @j = @j   1
        IF @j < @cur_nb_rows
            SET @sql = @sql   ', TMP'  CONVERT(VARCHAR,@i)   CONVERT(VARCHAR,@j)   '.[VAL] AS [VAL'    CONVERT(VARCHAR,@j)   ']'
        ELSE
            SET @sql = @sql   ', NULL'
    END
    SET @j = 0
    SET @sql = @sql   '
FROM TMP AS TMP'   CONVERT(VARCHAR,@i)   '0'
    WHILE @j 1 < @nb_cols
    BEGIN
        SET @j = @j   1
        IF @j < @cur_nb_rows
            SET @sql = @sql   '
INNER JOIN TMP AS TMP'   CONVERT(VARCHAR,@i)   CONVERT(VARCHAR,@j)   ' ON TMP'   CONVERT(VARCHAR,@i)   CONVERT(VARCHAR,@j)   '.Row = '   CONVERT(VARCHAR,@j 1)  ' AND TMP'   CONVERT(VARCHAR,@i)   CONVERT(VARCHAR,@j)   '.UniqueID = '''   CONVERT(VARCHAR,@cur_id)   ''''
    END
    SET @sql = @sql   '
WHERE TMP'   CONVERT(VARCHAR,@i)   '0.Row = 1 AND TMP'   CONVERT(VARCHAR,@i)   '0.UniqueID = '''   CONVERT(VARCHAR,@cur_id)   ''''
    SET @j = 0
    SET @i = @i   1
END

PRINT(@sql)

EXEC(@sql)

Drop table #TempAppointmentTable

The output is

As you can see it considers the number of elements, so the day you'll have 100 rows for a product (p0003 or any other) it will have 101 columns :D You can modify that script for your neeeds I hope it can feets your needs

For any other question I'll be right there