CODEWITHSUNDEEP
sql-server
Jan
Jan

Reputation: 4216

Pivot a table on two value columns

I am trying to pivot a table on two columns (we did some normalization on our table but need to create a view for backwards compatibility). My new data structure is this:

CREATE TABLE #Products (
  product VARCHAR(30),
  market_year INT,
  value INT,
  description varchar(20));

INSERT INTO #Products VALUES('Corn', 2003, 100, 'bad');
INSERT INTO #Products VALUES('Beer', 2003, 200, 'not so bad');
INSERT INTO #Products VALUES('Beef', 2003, 150, 'good');

INSERT INTO #Products VALUES('Corn', 2004, 10, 'doo');
INSERT INTO #Products VALUES('Beer', 2004, 20, 'foo');
INSERT INTO #Products VALUES('Beef', 2004, 10, 'bar');

which looks Like:

SELECT * FROM #Products p;

product market_year value   description
Corn        2003    100     bad
Beer        2003    200     not so bad
Beef        2003    150     good
Corn        2004    10      doo
Beer        2004    20      foo
Beef        2004    10      bar

The result I need should look like the following: 

market_year |corn_value |corn_description | beer_value | beer_description |beef_value | beef_description
2003        | 100       |bad              | 150        | NOT so bad       |150        | good
2003        | 10        |doo              | 15         | foo              |15         | bar

I know I could do this with two separate statements with one pivot each on market_year and join them by market_year but that would require this table to be read twice. Is there a more elegant way to solve this issue? (my table has approximately 7 million records split by 70 columns that need to be reverted to 140 columns)

Upvotes: 0

Views: 80

Answers (2)

falgun modi
falgun modi

Reputation: 45

select * from ( select market_year,product, value from #products p ) src pivot ( sum(value) for product in (Beer, Com, Beef) ) piv;

try this it will help you to get some idea

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

You can try to use dynamic pivot

declare two var @CONTENT to represnt pivot sql query CASE WHEN with MAX then use @sql to connecte your query sql and use execute execute this sql dynamically.

DECLARE @SQL VARCHAR(MAX),@CONTENT VARCHAR(MAX)
SELECT  @CONTENT = STUFF((
    SELECT ', '+'MAX(CASE WHEN product = '''+ product + ''' THEN [VALUE] END) AS '''+product+'_value'',
        MAX(CASE WHEN product = '''+ product + ''' THEN [description] END) AS '''+product+'_value'''
    FROM #Products 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'')

SELECT @SQL = 'SELECT market_year,' + @CONTENT + ' FROM #Products GROUP BY market_year';
execute(@SQL)

sqlfiddle

Upvotes: 1

Related Questions