seo Question posted in
Some basic information to get you started with this can be found here.

How do I transpose rows into columns with specified column names? – SEO

I would like to construct a query to map products and their associated pictures to match a target database schema. My source database stores SEO URLs for each Product in a Picture table using Product_Picture_Mapping as a two-way mapping table. Each Product table can thus have 0 to n pictures associated with it.

SELECT 
    Name, Price, SeoFilename
FROM Product prod
    JOIN Product_Picture_Mapping map
    ON prod.Id = map.ProductId 
    JOIN Picture pict
    ON pict.Id = map.PictureId  


Name            Price   SeoFilename
-----------------------------------
Strawberries    11      strawberry
Strawberries    11      strawberry_1
Pineapples      10      pineapples
Banana          10      banana
Banana          10      banana_1
Orange          11      orange

Unfortunately, the target database’s product table has 0 to 3 SEO URLs stored as fields. This makes writing the query quite difficult as I would need to transpose the source’s rows into named columns like so:

Name            Price   MainImageUrl    OtherImageUrl1  OtherImageUrl2
Banana          10      banana          banana_1        null
Orange          11      orange          null            null
Pineapples      10      pineapples      null            null    
Strawberries    11      Strawberry      Strawberry_1    null

I’ve tried using the recommended PIVOT function, but it can only generate aggregate values as it requires an aggregate function. I’ve seen other methods, but they are usually single joins to subqueries.

Here’s a link to my db fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=786b419936007c85f7f71f0defe5b829

Tags:

3

Answers


  1. 0

    I think you want conditional aggregation:

    SELECT Name, Price,
       MAX(CASE WHEN seqnum = 1 THEN SeoFilename END) as SeoFilename_1,
       MAX(CASE WHEN seqnum = 2 THEN SeoFilename END) as SeoFilename_2,
       MAX(CASE WHEN seqnum = 3 THEN SeoFilename END) as SeoFilename_3,
       MAX(CASE WHEN seqnum = 4 THEN SeoFilename END) as SeoFilename_4
FROM (SELECT Name, Price, SeoFilename,
             ROW_NUMBER() OVER (ORDER BY name, price SeoFilename) as seqnum
      FROM Product p JOIN
           Product_Picture_Mapping ppm
           ON p.Id = ppm.ProductId JOIN
           Picture pic
           ON pic.Id = ppm.PictureId  
     ) pi
GROUP BY Name, Price;

    This does not produce exactly the result set in your question. But I think it is what you want.

    Here is a db<>fiddle.

    Login or Signup to reply.
  2. 0

    I would suggest to user Pivot function together with ROW_NUMBER() OVER ( partition by name, price ORDER BY SeoFilename)

    With temp as (
SELECT 
    Name,
    Price, 
    SeoFilename,
    ROW_NUMBER() OVER (partition by Name, Price ORDER BY SeoFilename) as seqnum

FROM 
    Product prod
    JOIN Product_Picture_Mapping map
    ON prod.Id = map.ProductId 
    JOIN Picture pict
    ON pict.Id = map.PictureId
)
select
    Name,
    price,
    "1" as MainImageUrl,
    "2" as OtherImageUrl1,
    "3" as OtherImageUrl2,
    "4" as OtherImageUrl3,
    "5" as OtherImageUrl4
 from
        Temp
    pivot
    (
         min(SeoFilename)
         for seqnum in ( 1,2,3,4,5 ) -- Depends on how deep is it
    );

    • The “ROW_NUMBER() OVER (partition by Name, Price ORDER BY SeoFilename)” created a sequence number (“1″,”2″,”3” etc) for each rows by different name and price.

    • The Pivot function will transpose the result for by name and price based on the sequence number given from the seqnum column.

    • The min aggregation function is actually doing nothing, as there will be only one SeoFilename for each seqnum under the same name and price. It was just there to full-fill the requirement of the pivot function.
    Login or Signup to reply.
  3. 0

    The trick is twofold. First you need to add a way to partition the data per name. Rownumber will aid in this. Secondly you need to pivot the old fashion way.

    Rownumber is windowed function and you can add a partition to it. this partition is needed to determine if something will be the first, second, third transposed column. The only downside to this method, is the need to hardcode the amount of transposed columns

    Old fashioned pivots work with group by and case when statements. To circumvent the required aggregation, you can use the MAX statement, since the case when will basically ignore the max statement.

    if OBJECT_ID('tempdb..#test') is not null
begin
    drop table #test
end

create table #test
(
name varchar(50)
,price int
,seofilename varchar(50)
)

insert into #test
values 
('apple', 10, 'apple1'),
('apple',10,'apple4'),
('pear',23,'pear1'),
('pear',23,'pear5'),
('banana',56,'banan9'),
('banana',56,'banan6'),
('banana',56,'banan')

select
    name,
    price,
    max(case when rownum = 1 then seofilename else null end) as PrimaryFileName,
    max(case when rownum = 2 then seofilename else null end) as SecondaryFileName,
    max(case when rownum = 3 then seofilename else null end) as TertiaryFileName
from
(
select 
    name, 
    price, 
    seofilename,
    ROW_NUMBER() over (partition by name order by name) as rownum 
from 
    #test
) Q
group by
    name,
    price
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search