SQL Multiple rows to single row with multiple columns

Question

I have these data in a table in SQL:

Product    Size     Colour    Number

-------    ----     ------    ------

Jacket       S       Red         3

Jacket       M       Red         2

Jacket       S       Green       5

Shirt        S       Blue        1

Shirt        L       Blue        9

and I want to convert it dynamically without knowing how many rows I might have by combining all the same product rows to something like this:

Product   SRed    MRed    SGreen     SBlue    LBlue

-------   ----    ----    ------     -----    -----

Jacket     3       2        5

Shirt                                  1        9

Is this possible and how?

Thanks

is the number of columns fixed or do you want to have it dynamic? If dynamic, would you be satisfied with a table (product, colorSize, amount) ? — maraca
– maraca, Commented Jul 31, 2015 at 18:21
possible duplicate of stackoverflow.com/questions/21692871/… — Vamsi Prabhala
– Vamsi Prabhala, Commented Jul 31, 2015 at 18:23
it is possible, but it violates relational database main principle, try to avoid it — Iłya Bursov
– Iłya Bursov, Commented Jul 31, 2015 at 18:27

kevin d · Accepted Answer · 2015-07-31 19:50:25Z

I'm not sure if you are looking for something dynamic or static, but this will solve a static issue:

declare  @SRed As int
set @SRed =  (Select Number from YourTable where Size = 'S' and Colour = 'Red' and Product = 'Jacket')
declare @MRed As int
set @MRed = (Select Number from YourTable where Size = 'M' and Colour = 'Red' and Product = 'Jacket')
declare @SGreen As int
set @SGreen = (Select Number from YourTable where Size = 'S' and Colour = 'Green' and Product = 'Jacket')
declare @SBlue As int
set @SBlue = (Select Number from YourTable where Size = 'S' and Colour = 'Blue' and Product = 'Shirt')
declare @LBlue As int
set @LBlue = (Select Number from YourTable where Size = 'L' and Colour = 'Blue' and Product = 'Shirt')
Create Table #Jacket(Product varchar(20), SRed int, MRed int, SGreen int, IsInTable int)

insert into #Jacket(Product, SRed, MRed, SGreen)
Values(
'Jacket',
@SRed,
@MRed,
@SGreen
)
insert into #Jacket(Product, SRed, MRed, SGreen)
Values(
'shirt',
0,
0,
0
)

Create Table #Shirt(Product varchar(20), SBlue int, LBlue int)
insert into #Shirt(Product, SBlue, LBlue)
Values(
'Shirt',
@SBlue,
@LBlue
)

insert into #Shirt(Product, SBlue, LBlue)
Values(
'Jacket',
0,
0
)
Select s.Product, j.SRed, j.MRed, j.SGreen, s.SBlue, s.LBlue  
from #Shirt s
left outer join #Jacket j on j.Product = s.Product

Collectives™ on Stack Overflow

SQL Multiple rows to single row with multiple columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related