SQL Query Dividing 1 column to 3 columns

Question

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).

What's the expected behavior when it's 2017? Do you want another column to automatically show up in the result? — jarlh, Aug 30 '16 at 08:25
Possible duplicate of [Convert Rows Into Columns SQL Server](http://stackoverflow.com/questions/33623321/convert-rows-into-columns-sql-server) — Moumit, Aug 30 '16 at 08:46

score 2 · Answer 1 · edited Aug 30 '16 at 09:23

2

You can use PIVOT for fixed columns like the following:

SELECT *
FROM
(
    SELECT a.Itemcode, a.[Description], a.Model, b.[Year], b.Condition
    FROM        tbl_Masterlist a 
    INNER JOIN  tbl_Conditions b ON a.Itemcode = b.Itemcode
) src
PIVOT
(
  MAX(Condition) FOR [[Year]] IN([2014], [2015], [2016])
) piv

edited Aug 30 '16 at 09:23

TheGameiswar

27,855
8
56
94

answered Aug 30 '16 at 08:58

Jahirul

25
1
7

score 1 · Accepted Answer · answered Aug 30 '16 at 08:34

1

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;

answered Aug 30 '16 at 08:34

Chanukya

5,833
1
22
36

Thank you very much sir. – Redentoru Aug 30 '16 at 08:52

score 0 · Answer 3 · edited Aug 30 '16 at 08:54

0

WITH empact AS 
    (SELECT Itemcode,a.Description,a.Model,b.Condition,b.Year 
FROM tbl_Masterlist a JOIN tbl_Conditions b ON a.Itemcode=b.Itemcode)

SELECT   Itemcode,Description,Model, [2005],   [2006], [2007],   [2008]
FROM   [dbo].[PivotExample] 
PIVOT
(
       max(condtion)
       FOR [Year] IN ([2005], [2006], [2007], [2008])
FROM empact) AS P

edited Aug 30 '16 at 08:54

Moumit

8,314
9
55
59

answered Aug 30 '16 at 08:44

Thiru

64
1
1
10

score 0 · Answer 4 · edited Aug 30 '16 at 09:23

0

SELECT *
FROM
(
    SELECT a.Itemcode, a.[Description], a.Model, b.[Year], b.Condition
    FROM        tbl_Masterlist a 
    INNER JOIN  tbl_Conditions b ON a.Itemcode = b.Itemcode
) src
PIVOT
(
  MAX(Condition) FOR [[Year]] IN([2014], [2015], [2016])
) piv

edited Aug 30 '16 at 09:23

TheGameiswar

27,855
8
56
94

answered Aug 30 '16 at 09:02

Jahirul

25
1
7

score 0 · Answer 5 · answered Aug 30 '16 at 10:11

You can use below standard SQL perspective :

SELECT  m.Itemcode,m.Description,m.Model
       ,SUM(CASE WHEN c.Years='2014' THEN c.Conditions END) AS '2014'
       ,SUM(CASE WHEN c.Years='2015' THEN c.Conditions END) AS '2015'
       ,SUM(CASE WHEN c.Years='2016' THEN c.Conditions END) AS '2016' 
  FROM tbl_Masterlist AS m 
  LEFT JOIN tbl_Conditions AS c ON m.Itemcode = c.Itemcode
  GROUP BY m.Itemcode,m.Description,m.Model

Share my test script:

CREATE TABLE tbl_Masterlist(Itemcode varchar(50),Description varchar(50),Model varchar(50));

CREATE TABLE tbl_Conditions(Itemcode varchar(50),Conditions INT,Years varchar(20));

INSERT INTO tbl_Masterlist(Itemcode,Description,Model) 
VALUES('I1','Item1','M1')
,('I2','Item2','M2')
,('I3','Item3','M3')

INSERT INTO tbl_Conditions(Itemcode,Conditions,Years)
VALUES('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')

Hope it could help you.

SQL Query Dividing 1 column to 3 columns

5 Answers5