SQL查询将一列分为三列

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

我在将列年划分为3列时遇到了麻烦，其中填充了项目条件，基于年份选择(3年范围)。

5 个解决方案

#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;

#2

You can use PIVOT for fixed columns like the following:

对于固定列可以使用PIVOT，如下所示:

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

#3

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

#4

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

#5

You can use below standard SQL perspective :

您可以使用以下标准SQL透视图:

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.

希望它能对你有所帮助。

#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;

#2