SQL Create new column(s) if row value exists

Question

I have a large data set that looks like the below:

NAME    Value
Dan     1 
Dan     92
Dan     A4
Steve   1
Steve   B10
John    4

I'm trying to convert it into a table like:

Name    Value1    Value2    Value3
Dan     1         92        B10
Steve   1         B10       Null
John    4         Null      Null

So there is an unknown amount of rows and I'd like to create a new column for every value when it exists. Anyone have an idea of how to do this in SQL?

please consider providing us with a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) which will help the community in answering your question. In other words what have you tried so far and why is it not working? — AGE, Dec 10 '19 at 18:50

Mike Petri · Accepted Answer · 2019-12-10T19:09:39.433

0

The example you provided would work perfectly using PIVOT, but you need to supply a category to the values to do the pivot.

e.g.

NAME    Category    Value
Dan     Value1      1 
Dan     Value2      92
Dan     Value3      A4
Steve   Value1      1
Steve   Value3      B10
John    Value1      4

Then your results would be like this

Name    Value1  Value2    Value3
Dan     1       92        A4
Steve   1       NULL      B10
John    4       NULL      NULL

Here's Microsoft's documentation: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

To do this dynamically, please read this post. It handles the same situation well. SQL Server dynamic PIVOT query?

edited Dec 10 '19 at 19:09

answered Dec 10 '19 at 18:47

Mike Petri

570
3
10

Thanks and yes, I understand. However, the quantity of rows is unknown and varies wildly between 1 and 50. I was wondering if there was a way to somehow utilize a loop with a case statement – dcrowley01 Dec 10 '19 at 18:53
I've updated the answer to include a link on how to do this dynamically. – Mike Petri Dec 10 '19 at 19:10
Hello FlogDonkey, the dynamic solution at https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query worked perfectly. – dcrowley01 Dec 12 '19 at 20:25

score 0 · Answer 2 · answered Dec 10 '19 at 20:29

To pivot data you need something to pivot it by.

In this case it can be generated using ROW_NUMBER.

For example:

--
-- sample data
-- 
create table yourlargetable (
 id int identity(1,1) primary key,
 name nvarchar(30),
 value nvarchar(30)
);

insert into yourlargetable (name, value) values
('jane', 'val1'), ('jane', 'val2'), ('jane', 'val3'), 
('john', 'val4'), ('john', 'val5');

--
-- declare a few variables
--
declare @DynSql nvarchar(max);
declare @Cols nvarchar(max);
declare @ColTotal int;

--
-- how many columns are needed
-- 
select top 1 @ColTotal = count(*)
from yourlargetable
group by name 
order by count(*) desc;

--
-- generate a string with column names
--
with RCTE_NUMS as
(
  select 1 as n
  union all
  select n+1
  from RCTE_NUMS
  where n < @ColTotal
)
select @Cols = concat(@Cols+', ', quotename(concat('Value', n)))
from RCTE_NUMS
order by n;

--
-- create the dynamic sql string
-- 
set @DynSql = 'select *'+ char(10) +
'from ('+
'select name, value '+ char(10) +
', concat(''Value'', row_number() over (partition by name order by value)) col '+ char(10) +
'from yourlargetable) s'+ char(10) +
'pivot (max(value) '+ char(10) +
'for col in ('+ @Cols +')) p'+ char(10) +
'order by name';

-- select @DynSql;

--
-- run the dynamic sql
-- 
exec sp_executesql @DynSql;

Returns:

name    Value1  Value2  Value3
jane    val1    val2    val3
john    val4    val5    NULL

SQL Create new column(s) if row value exists

2 Answers2