Need help w/ across T-SQL code !!!

[ctudose]

I need a T-SQL routine that takes Table1 in this format:
(
DUNS [char] (9)-with duplicate values,
INDICATOR [char] (4)-
)

111111111 abc1
111111111 abc2
111111111 abc3
.....................
111111111 abc6
222222222 def1
222222222 def2
222222222 def3
333333333 ghi1

and creates a new table Table2 in this format:
(
DUNS [char] (11)- only UNIQUE VALUES,
IND1 [char] (4)
IND2 [char] (4)
IND3 [char] (4)
IND4 [char] (4)
IND5 [char] (4)
IND6 [char] (4)
)

111111111 abc1 abc2 abc3 abc4 abc5 abc6
222222222 def1 def2 def3 NULL NULL NULL
333333333 ghi1 NULL NULL NULL NULL NULL

ANY HELP IS VERY MUCH APPRECIATED
Thanks

 

[smin]

This can be done with a UDF with a table data type. Try this code and see if this works.

create function getValue(
@inputparm1 char(9),
@inputparm2 int )
returns char(4)
as
begin
declare @outvalue char(4)
declare @table1 table( idn int IDENTITY(1,1),
col1 char(9),
col2 char(4))

-- fill this table with each unique duns only
insert @table1
select duns, indicator
from theTable
where duns = @inputparm1
order by indicator

-- select the value accroding to the order
select @outvalue = col2 from @table1
where idn = @inputparm2

return(@outvalue)
end

Now call this function like below;

select distinct duns, dbo.getvalue(duns, 1),
dbo.getvalue(duns, 2),
dbo.getvalue(duns, 3),
dbo.getvalue(duns, 4),
dbo.getvalue(duns, 5),
dbo.getvalue(duns, 6)
from theTable