SQL Converting multiple rows into multiple columns

Question

I have a table that looks like this:

Sku_Code  Channel Rank Category    Website      Date
123        US     28    Toys     www.foo.com    2021-06-07 
123        US     13    Games    www.lolo.com   2021-06-07
328        CA     12    Toys     www.lo.com     2021-05-12
123        US     2     Games    www.foo.com    2021-06-05

I would like to pivot this table so all ID information falls in one row...like this:

Sku_Code  Channel   Category  Category_1    Website       Website_1       Date
     123  US        Toys      Games        www.foo.com   www.lolo.com   2021-06-07 
     328  CA        Toys                   www.lo.com                   2021-05-12
     123  US        Games                  www.foo.com                  2021-06-05

It's a fairly large table so wondering whats the best/fastest way to do this? I know there is a pivot function I could use but do not now how to apply it in this situation.

I'm fairly new to SQL so any help would be appreciated.

I don't follow the question. Id 123 is repeated in the results. — Gordon Linoff
– Gordon Linoff, Commented Jul 1, 2021 at 16:21
If an ID can have an arbitrary number of categories, this is not possible in SQL. The SQL language has a strict requirement for the number and types of columns to be known at query compile time, before looking at any data. — Joel Coehoorn
– Joel Coehoorn, Commented Jul 1, 2021 at 16:22
Which dbms are you using? Some products have PIVOT, others not. — jarlh
– jarlh, Commented Jul 1, 2021 at 19:17

Joel Coehoorn · Accepted Answer · 2021-07-01 16:36:44Z

1

It's not clear whether an ID can have an arbitrary number of categories. If so, this is not possible in normal SQL alone. The SQL language has a strict requirement for the number and types of columns to be known at query compile time, before looking at any data.

That doesn't mean what you want to do can't happen at all... just that the solution will be more involved. For example, you may need to do the pivot in your reporting tool or client code. The other alternative is dynamic sql over three steps: First, run a query to determine how many categories you will need. Second, use the information from step one to build a new SQL statement on the fly, probably involving an additional join back to the same table for each category, using the PIVOT keyword, or both. Finally, execute the SQL statement built in step two.

edited Jul 1, 2021 at 16:36

answered Jul 1, 2021 at 16:25

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sluna Over a year ago

Thank you for your response. The ID column is a product identifier so it can be any type of number. The ID column can have an arbitrary number of categories so I'll have to most likely go with your more involved solution. Thank you for your help.

Collectives™ on Stack Overflow

SQL Converting multiple rows into multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related