How to create new columns based on values from another column dynamically

Question

I would like to create new columns based on values from another column dynamically.

For example ( fruits and quantity may be changed from time to time so the code should be able to create new columns as needed): I can do a case statement to create the two new columns : Banana, Apple.But how can I do it dynamically? - i.e tomorrow I'll get a new fruit like "Melon" or one fruit will be omitted.

ID FRUIT QUANTITY
22 Banana 5
33 Banana 1
44 Apple  3

Output

ID FRUIT QUANTITY Banana Apple
22 Banana 5         1      0
33 Banana 1         1      0
44 Apple  3         0      1

You're wanting to transform rows into columns which can be done via pivot. However, because you want to dynamically create columns, you would have to go one step further and build a Dynamic SQL statement. This is because Pivot expects you to define the columns (i.e. it's static), it won't dynamically create them from your FRUIT data. Generally speaking, it's easier and more practical to perform pivoting in whatever tool you use to present (i.e. display) the data. — Jonathon Ogden
– Jonathon Ogden, Commented Jul 17, 2016 at 10:29

Eralper · Accepted Answer · 2016-07-17 10:51:17Z

1

Could you please try following dynamic SQL pivot query

--create table FruitQty (ID int, FRUIT varchar(25), QUANTITY int)
--insert into FruitQty select 1,'Melon',5
--insert into FruitQty select 2,'Apple',3
--insert into FruitQty select 3,'Apple',5

DECLARE @Fruits nvarchar(max)
SELECT @Fruits =
 STUFF(
 (
  select distinct ',[' + Fruit + ']'
  from FruitQty
  for xml path('')
 ),
 1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select
 *
from (
 select
  FRUIT,
  QUANTITY
 from FruitQty
 ) Data
PIVOT (
 SUM(QUANTITY)
 FOR FRUIT
 IN (
  ' + @Fruits + '
 )
) PivotTable
'
exec sp_executesql @SQL

You can find details and samples on dynamic SQL pivot query at referenced SQL Server tutorial

I hope it helps

answered Jul 17, 2016 at 10:51

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create new columns based on values from another column dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related