0

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
user3680876
  • 17
  • 4
  • 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 http://stackoverflow.com/questions/11617713/how-to-create-a-pivottable-in-transact-sql and another: http://stackoverflow.com/questions/2824475/row-convert-to-column-in-sql-2008 – xQbert Jun 30 '14 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 Jun 30 '14 at 18:02
  • there is limited number of TextTypeID – user3680876 Jun 30 '14 at 18:06
  • possible duplicate of [Making row values into column values -- SQL PIVOT](http://stackoverflow.com/questions/9117955/making-row-values-into-column-values-sql-pivot) – Anon Jun 30 '14 at 18:17

1 Answers1

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
DavidN
  • 171
  • 5