Redentoru Redentoru - 3 months ago 9
SQL Question

SQL Query Dividing 1 column to 3 columns

I have this tables:

tbl_Masterlist

|Itemcode|Description|Model|
| I1 | Item1 | M1 |
| I2 | Item2 | M2 |
| I3 | Item3 | M3 |


tbl_Conditions

|Itemcode| Condition| Year |
| I1 | 1 | 2014 |
| I2 | 2 | 2014 |
| I3 | 2 | 2014 |
| I1 | 3 | 2015 |
| I2 | 2 | 2015 |
| I3 | 2 | 2015 |
| I1 | 3 | 2016 |
| I2 | 1 | 2016 |
| I3 | 3 | 2016 |


this is the expected output.

| Itemcode | Description | Model | 2014 | 2015 | 2016 |
| I1 | Item1 | M1 | 1 | 3 | 3 |
| I2 | Item2 | M2 | 2 | 2 | 1 |
| I3 | Item3 | M3 | 2 | 2 | 3 |


I'm having trouble on dividing the column year into to 3 columns, populated with the items condition, base on the year select (3 years range).

Answer
select *
from 
(
select  A.Itemcode, A.[Description], A.Mode,
B.Condition, B.[Year] from tbl_Masterlist A join tbl_Conditions B ON A.Itemcode = B.Itemcode
) src
pivot
(
  MAX(CONDITION)
  for YEAR in ([2014], [2015], [2016])
) piv;
Comments