Move duplicate values of a row to columns in SQL Server

Question

I have a table with the following sample data:

Name            Value
-----------------------
Year            1990
Year            1991
Year            1992
Cash            2000
Cash            4000
Cash            6000
Tax             Paid
Tax             Unpaid
Tax             Pending
Liability       3500
Liability       8500
Liability       9500

I want that data in the following format:

Name            Value1        Value2        Value3
-----------------------------------------------------
Year            1990          1991          1992
Cash            2000          4000          6000
Tax             Paid          Unpaid        Pending
Liability       3500          8500          9500

Please note, the number of columns in result table should be dynamic i.e., number of value columns = number of records for an item in original table.

In the example, each item has 3 records so there will be 3 value columns in result table.

Please guide.

Update:

I used below query and it returned data in correct format, but still clueless how to identify number of columns in resultant table. In below example fixed 5 columns are there (name, value1, value2, value3, value4).

with cte as
(
 select name ci,value,
        row_number() over(partition by [name] order by value) as rn
 from TABLE
)
select distinct ci as [name],
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=1) value1,
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=2) value2,
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=3) value3,
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=4) value4
from cte

What if there are 4 rows, or 5? What baout 10? Is it at most always 3? What didn't work in your attempts? — Thom A
– Thom A ♦, Commented Apr 14, 2020 at 16:50
Does this answer your question? SQL server : Convert rows into columns — Thom A
– Thom A ♦, Commented Apr 14, 2020 at 16:57
Hi @Larnu, number of rows are not fixed. It can be 4,5 or 10. I guess, Pivot will not help as we do not have to transpose all "Name" to column but "values" to columns. — WorldJohn
– WorldJohn, Commented Apr 14, 2020 at 16:59
Then you need a dynamic Pivot, there's 100's if examples of these on SO. — Thom A
– Thom A ♦, Commented Apr 14, 2020 at 17:00

lptr · Accepted Answer · 2020-04-15 11:28:19Z

create table #t
(
Name varchar(50),
Value varchar(50)
)
insert into #t(Name, Value)
values
('Year', '1990'),
('Year', '1991'),
('Year', '1992'),
('Cash', '2000'),
('Cash', '4000'),
('Cash', '6000'),
('Tax', ' Paid'),
('Tax', ' Unpaid'),
('Tax', ' Pending'),
('Liability', '3500'),
('Liability', '8500'),
('Liability', '9500'),
('Liability', '1500'),
('Liability', '2500'),
('Liability', '4500');


declare @sql nvarchar(max) = stuff(
(
select ',[value'+cast(rownum as varchar(20))+']'
from
(
select top((select max(cnt) from (select count(*) as cnt from #t group by Name) as t)) row_number() over(order by a.v) as rownum
from 
(
values(cast(null as bit)),(null),(null),(null),(null),(null),(null),(null),(null),(null)
) as a(v)
cross join
(
values(cast(null as bit)),(null),(null),(null),(null),(null),(null),(null),(null),(null)
) as b(v)
cross join
(
values(cast(null as bit)),(null),(null),(null),(null),(null),(null),(null),(null),(null)
) as c(v)
) as nums
order by rownum
for xml path('')), 1, 1, N'');

select @sql = N'
select *
from
(
select *, ''value''+cast(row_number() over(partition by Name order by (select null)) as varchar(20)) as colname
from #t
) as t
pivot
(
max(value) for colname in (' + @sql + N')
) as unpv
';

exec(@sql);

--
drop table #t;

Collectives™ on Stack Overflow

Move duplicate values of a row to columns in SQL Server

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related