CODEWITHSUNDEEP
sqlsql-server
JPCS
JPCS

Reputation: 311

Convert row into column like pivot

Table is:

  +----+------+
  | Id | Name |
  +----+------+    
  | 1  | aaa  |
  | 1  | bbb  |
  | 2  | ccc  |
  | 2  | ddd  |
  | 3  | eee  |
  +----+------+

Required output:

+----+---------------------++---------------------+
| Id |        colum1       |   column2            |
+----+---------------------+ +--------------------+ 
|  1 | aaa                 | |   bbb              |
+----+---------------------++---------------------+
+----+---------------------+ +--------------------+ 
|  2 | ccc                | |   ddd               |
+----+---------------------++---------------------+
+----+---------------------+ +--------------------+ 
|  3 | eee                 | |   null             |
+----+---------------------++---------------------+

I've been trying on 'with' a and pivot but it seems not in the right way I want a column if I have more than one id

like the image

Upvotes: 2

Views: 59

Answers (3)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use PIVOT for making this.

DECLARE @Tbl  TABLE ( Id INT, Name VARCHAR(10))
INSERT INTO @Tbl VALUES
(1, 'aaa'),
(1, 'bbb'),
(2, 'ccc'),
(2, 'ddd'),
(3, 'eee')

SELECT Id, [1] column1, [2] column2 FROM (
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Name) RN 
    FROM @Tbl ) AS SRC PIVOT (MAX(Name) FOR RN IN ([1], [2])) PVT

Result:

Id          column1    column2
----------- ---------- ----------
1           aaa        bbb
2           ccc        ddd
3           eee        NULL

Upvotes: 2

Marta B
Marta B

Reputation: 448

Using row_number() build a dynamic query to execute()

--example table
create table #t (Id int, Name varchar(100))
insert into #t values (1,'aaa'),(1,'bbb'),(2,'ccc'),(2,'ddd'),(3,'eee')

-- number of columns to create
declare @columns int
select top 1 @columns = count(*) from #t group by id order by COUNT(*) desc

--build a query
declare @i int = 2, @qry varchar(max) = 'select Id, column1 = max(case when ord = 1 then name end)'

while @i<=@columns begin
    select @qry = @qry + ', column'+cast(@i as varchar(5))+' = max(case when ord = '+cast(@i as varchar(5))+' then name end)'
    set @i = @i + 1
end

select @qry = @qry + '  from (select *, ord = row_number() over (partition by id order by name)
                              from #t
                             ) t
                        group by id'

--execute the query
execute (@qry)

drop table #t

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() & do aggregation if you have a some limited amount of names else you would need to use dynamic SQL for this:

select id, 
       max(case when seq = 1 then name end) as col1,
       max(case when seq = 2 then name end) as col2,
       max(case when seq = 3 then name end) as col3,
       . . .
from (select t.*, row_number() over (partition by id order by name) as seq
      from table t
     ) t
group by id;

Upvotes: 3

Related Questions