0

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

Sam FarajpourGhamari
  • 14,601
  • 4
  • 52
  • 56
user3170
  • 61
  • 2
  • 8
  • 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 Jul 31 '15 at 18:21
  • possible duplicate of http://stackoverflow.com/questions/21692871/combine-multiple-rows-into-multiple-columns-dynamically-in-sql-server – Vamsi Prabhala Jul 31 '15 at 18:23
  • it is possible, but it violates relational database main principle, try to avoid it – Iłya Bursov Jul 31 '15 at 18:27

1 Answers1

0

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
kevin d
  • 1
  • 2