How to convert data using SQL Pivot table

Question

I need to convert below mentioned table rows to columns using pivot table (dynamic column) where in table Article Number,Article Name,Catalog Code are fixed columns .Your help will be highly appreciated

ArticleNumber   ArticleName CatalogCode AttributeName   AttributeValue
s123              abc         1          SportsCategory     Rakesh
s123              abc         1          Brand              Amar
s123              abc         1          ProductDivision    Ball
s123              abc         1          MarketingDivision  Boss
s222              XYZ         1          SportsCategory     Rakesh5
s222              XYZ         1          Brand              Red
s222              XYZ         1          ProductDivision    blue
s222              XYZ         1          SportsCategory     Rakesh8

OutPut:

ArticleNumber   ArticleName CatalogCode SportsCategory  Brand   ProductDivision MarketingDivision
s123             abc          1          Rakesh         Amar     Ball           Boss
s222             abc          1          Rakesh5        Red      blue           Rakesh8

Possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Feb 24 '16 at 13:32
My answer is usefull or not if it is usefull then mark it as accept. -Amardeep P — Chanukya, Feb 25 '16 at 15:16

Chanukya · Answer 1 · 2016-02-24T13:46:35.513

1

SELECT *
FROM
(
  SELECT ARTICLE, NAME, CODE, ATTRIBUTENAME, ATTRIBUTEVALUE
  FROM #TABLE1
) D
PIVOT
(
  MAX(ATTRIBUTEVALUE)
  FOR ATTRIBUTENAME IN  ([SPORTSCATEGORY],[BRAND],[PRODUCTDIVISION],[MARKETINGDIVISION])
) PIV;``

edited Feb 24 '16 at 13:46

answered Feb 24 '16 at 13:35

Chanukya

5,833
1
22
36

Please add some explanation to your answer so it can be of use to later questioners as well. – Brody Feb 24 '16 at 22:58

score 0 · Accepted Answer · answered Feb 24 '16 at 13:34

it is a simple pivot

DECLARE @Table1 TABLE 
    (Article varchar(4), Name varchar(3), Code int, Attribute varchar(17), AttributeValue varchar(7))
;

INSERT INTO @Table1
    (Article, Name, Code, Attribute, AttributeValue)
VALUES
    ('s123', 'abc', 1, 'SportsCategory', 'Rakesh'),
    ('s123', 'abc', 1, 'Brand', 'Amar'),
    ('s123', 'abc', 1, 'ProductDivision', 'Ball'),
    ('s123', 'abc', 1, 'MarketingDivision', 'Boss'),
    ('s222', 'XYZ', 1, 'SportsCategory', 'Rakesh5'),
    ('s222', 'XYZ', 1, 'Brand', 'Red'),
    ('s222', 'XYZ', 1, 'ProductDivision', 'blue'),
    ('s222', 'XYZ', 1, 'SportsCategory', 'Rakesh8')
;

Select Article, Name, Code, [SportsCategory],[Brand],[ProductDivision],[MarketingDivision] from (
select Article, Name, Code, Attribute, AttributeValue from @Table1)T
PIVOT (MAX(AttributeValue) FOR Attribute IN ([SportsCategory],[Brand],[ProductDivision],[MarketingDivision]))P

How to convert data using SQL Pivot table

2 Answers2