SQL - create new columns with duplicate values

Question

I would like to change table like this:

ID  Value
1   A
1   B
2   C
2   A
3   D

To this:

ID  Value1  Value2
1   A       B
2   C       A
3   D

This is just sample tables but shows what i need to do. Actually I have table with 1669 rows. There is 1500 distinct ID and 84 distinct Values. The problem is that one ID can have few options. I want to make easy use table for changing/adding values and then import it back again to sql.

I'm using MS SQL server 2012. I looked for pivot but still not sure how to do it — Piotrek O, May 17 '18 at 13:58
Will there always be exactly 1 or 2 values per id, or can it be any number of values? — MatBailie, May 17 '18 at 14:00
Possible duplicate of [TSQL Pivot without aggregate function](https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Tab Alleman, May 17 '18 at 14:08

score 1 · Answer 1 · answered May 17 '18 at 13:58

1

You want conditional aggregation:

select ID,
       max(case when Seq = 1 then value end) [value1],
       max(case when Seq = 2 then value end) [value2]
from(select *,
       row_number() over (partition by ID order by Value) Seq
     from table 
    )t
group by ID;

answered May 17 '18 at 13:58

Yogesh Sharma

49,870
5
26
52

Works if they are max 2. OP is not clear. – paparazzo May 17 '18 at 14:14

score 0 · Answer 2 · answered May 17 '18 at 14:14

Try This

;WITH CTE(ID, Value)
AS
(
SELECT 1,'A' UNION ALL
SELECT 1,'B' UNION ALL
SELECT 2,'C' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 3,'D' 
)

SELECT Id,
      Value1=ISNULL(CAST('<S>'+REPLACE(Value,',','</S><S>')+'</S>'  AS XML ).value('/S[1]','varchar(max)'),''),
      Value2=ISNULL(CAST('<S>'+REPLACE(Value,',','</S><S>')+'</S>'  AS XML ).value('/S[2]','varchar(max)'),'')
FROM
(
SELECT DISTINCT ID, STUFF((SELECT ', '+Value FROM CTE i WHERE i.ID=o.ID 
FOR XML PATH ('')),1,1,'') AS Value
FROM CTE o
)dt

Result Demo: http://rextester.com/RGU24096

Id  Value1  Value2
------------------
1    A       B
2    C       A
3    D

score 0 · Answer 3 · answered May 17 '18 at 14:32

Will work with any number of repeated ID

declare @t table (id int, val char(1));
insert into @t values
    (1, 'A')
  , (1, 'B')
  , (2, 'C')
  , (2, 'A')
  , (3, 'D')
  , (4, 'a')
  , (4, 'b')
  , (4, 'c')
  , (4, 'd')
  , (4, 'e');
  select t.id, t.val1, t.val2
  from  ( select t.id, t.val as val1
               , lead(t.val) over  (partition by id order by val) as val2
               , ROW_NUMBER() over (partition by id order by val) as rn 
            from @t t
        ) t
  where rn % 2 = 1
  order by id, val1

SQL - create new columns with duplicate values

3 Answers3