How to display different values in same column into single Row in SQL server 2008

Question

I had a below two table which has

 ID      TextID     Description
 -------------------------------
 1        2          zzzz
 1        3          kkkk
 1        4          llll
 5        2          nnnn

 TextID      TextTypeID     
 -------------------------------
 1            R1
 2            R2
 3            R3
 4            R4

I want result for the ID like below using Case statement.Please advice.

 ID      R1      R2    R3     R4
 ---------------------------------
  1      null   zzzz   kkkk  llll

Either use of a pivot statement or case. without using Dynamic SQL you will have to know all the available column names. If the column names are variable, then you'll have to use dynamic SQL. Asked and answered alot on this site: here's one stackoverflow.com/questions/11617713/… and another: stackoverflow.com/questions/2824475/… — xQbert
– xQbert, Commented Jun 30, 2014 at 18:01
Do you know how many columns you are going to have, or is there a limited number of data specified in TextTypeID? — AHiggins
– AHiggins, Commented Jun 30, 2014 at 18:02
possible duplicate of Making row values into column values -- SQL PIVOT — Anon
– Anon, Commented Jun 30, 2014 at 18:17

DavidN · Accepted Answer · 2014-06-30 18:28:07Z

0

I named the tables #a, #b, and #c sense you did not provide names.

create table #c
(ID int not null primary key)

insert into #c (ID)
select distinct ID
from #a

declare @TextTypeID char(2)
declare @sql varchar(max)

declare r_cursor cursor for
select distinct TextTypeID
from #b
order by TextTypeID

open r_cursor
fetch next from r_cursor into @TextTypeID

while @@FETCH_STATUS = 0
BEGIN
    set @sql = 'alter table #c add [' + @TextTypeID + '] char(4)'
    exec (@sql)

    set @sql = 'update c set [' + @TextTypeID + '] = a.[Description]
    from #c c
    join #a a on c.ID = a.ID
    join #b b on a.TextID = b.TextID
    where b.TextTypeID = '''+ @TextTypeID + ''''
    exec (@sql)

    fetch next from r_cursor into @TextTypeID
END

close r_cursor
deallocate r_cursor

select *
from #c

answered Jun 30, 2014 at 18:28

DavidN

1715 bronze badges

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

Collectives™ on Stack Overflow

How to display different values in same column into single Row in SQL server 2008

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related