SQL Server thinks of arrays as tables, so it doesn't have an array data type....
to decode a string from a delimited list to a table, you can use one of the many Split functions contributed in the scrips section.
to convert rows of data back into a delimited list, you need to use a trick with FOR XML.
create function [dbo].[fn_split](
@STR varchar(8000),
@spliter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(8000))
as
begin
declare @spliterIndex int
SELECT @STR = @spliter + @STR + @spliter
INSERT @returnTable
SELECT SUBSTRING(@str,N+1,CHARINDEX(@spliter,@str,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@str)
AND SUBSTRING(@str,N,1) = @spliter
ORDER BY N
return
end
declare @skills table (Resource_Id int, Skill_Id varchar(20))
insert into @skills
select 101, 'sqlserver' union all
select 101, 'vb.net' union all
select 101, 'oracle' union all
select 102, 'sqlserver' union all
select 102, 'java' union all
select 102, 'excel' union all
select 103, 'vb.net' union all
select 103, 'java' union all
select 103, 'oracle'
---
select * from @skills s1
--- Concatenated Format
set statistics time on;
SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id
FROM @skills s2
WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below
ORDER BY Skill_Id
FOR XML PATH('')
),1,1,'') as [Skills]
FROM @skills s1
GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned
ORDER BY s1.Resource_Id
set statistics time off;
--- CrossTab Format
SELECT Resource_Id
,MAX(case when skill_id = 'Excel' then 'Yes' else '' end) as Excel
,MAX(case when skill_id = 'Java' then 'Yes' else '' end) as Java
,MAX(case when skill_id = 'Oracle' then 'Yes' else '' end) as Oracle
,MAX(case when skill_id = 'SQLServer' then 'Yes' else '' end) as SQLServer
,MAX(case when skill_id = 'VB.Net' then 'Yes' else '' end) as [VB.Net]
FROM @skills
Group by Resource_Id
Lowell