CODEWITHSUNDEEP
sql
Sluna
Sluna

Reputation: 189

SQL Converting multiple rows into multiple columns

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.

Upvotes: 0

Views: 44

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

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.

Upvotes: 1

Related Questions