Link to home
Start Free TrialLog in
Avatar of vzorn
vzornFlag for United States of America

asked on

create crosstab in SQL using PIVOT?

I have a sql view that has the following data.

OrderID   |  ProductID  |  Qty  |  Color |  Size  |
    1                 15            10        Blue      L
    1                  15            20       Blue      S

I need to get it into a view to look like this
OrderID    |  ProductID  |   Size1  |    Size2  |   Size3  |  Color  |   Size1Display  |  Size2Display |  Size3Display
     1                15                 20            10                           Blue             S                         L

Is this possible?  I have looked at PIVOT tables but not sure if this is a good solution.

Avatar of Zberteoc
Zberteoc
Flag of Canada image

Yes, you can do that but you have to come up with all distinct sizes, as I see you want to make 1 row for all sizes. You can pivot only if you generate the same number of columns from distinct rw values for a column. You will create a separate column for each size whtever they are, S,M,L,XL,XXL... and then you can pivot.
Avatar of vzorn

ASKER

Since I do not know the sizes entered I cannot use PIVOT?   The sizes could be different values depending on the item...s, m, l or 2, 4, 6 etc.
You can go and check how many distinct sizes you have at this moment with

SELECT DISTINCT size FROM youview

but if new sizes(codes) will be added you will lose them by pivoting. When pivoting you have to make sure that the values involved are always the same.

Avatar of vzorn

ASKER

Yes new sizes will be added and I am trying to create this data to fill a dataset to use on a report.
What method should I use to transform the data?
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vzorn

ASKER

Yes but how will I get the sizes into columns?
Avatar of vzorn

ASKER

That ended up being the best solution.  I grouped by product and color then filled in a grid.

Thanks.