select into an array - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select into an array

Author

Topic

lena_lafond
Starting Member

2 Posts

Posted - 2009-01-30 : 13:48:10

Hi,

Lets say I have a table of people and a table of colors, and a table that contains people's favorite colors.

Example:
John, Red
John, Blue
Jane, Green
Jane, Yellow

Is there a way I can get this to something like an array?
John, Red, Blue
Jane, Green, Yellow
?

Thanks,
Lena

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 13:58:01

[code]SELECT DISTINCT [name],
STUFF((SELECT ','+ CAST(Color) AS varchar(10)
FROM Table WHERE [name] = m.[name] FOR XML PATH('')),1,1,'') AS List
FROM Table m[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 14:19:34

This works for SQL 2000

--Create Table Variable
Declare @t table
(names varchar(10),Color varchar(10))

--Prepare sample data
insert @t
Select 'John', 'Red' union all
Select 'John', 'Blue' union all
Select 'Jane', 'Green' union all
Select 'Jane', 'Yellow'

Select names,
Max(Case when seq =1 then color else '' end)+ ',' +
Max(Case when seq =2 then color else '' end)+ ',' +
Max(Case when seq =3 then color else '' end)+ ',' +
Max(Case when seq =4 then color else '' end) as List
from
(SELECT m1.names,m1.color,(Select Count(*) from @t m2 where m1.names = m2.names
and m2.color<=m1.color )as seq
from @t m1) Z
Group by names

--output
names    Output
John	Blue,Red,,
Jane	Green,Yellow,,

lena_lafond
Starting Member

2 Posts

Posted - 2009-01-30 : 15:33:28

Thanks it works perfectly!

Lena

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 15:38:12

quote:
Originally posted by lena_lafond

Thanks it works perfectly!

Lena

Mostly Welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:11:57

quote:

Originally posted by sodeep

This works for SQL 2000

--Create Table Variable
Declare @t table
(names varchar(10),Color varchar(10))

--Prepare sample data
insert @t
Select 'John', 'Red' union all
Select 'John', 'Blue' union all
Select 'Jane', 'Green' union all
Select 'Jane', 'Yellow'

Select names,
Max(Case when seq =1 then color else '' end)+ ',' +
Max(Case when seq =2 then color else '' end)+ ',' +
Max(Case when seq =3 then color else '' end)+ ',' +
Max(Case when seq =4 then color else '' end) as List
from
(SELECT m1.names,m1.color,(Select Count(*) from @t m2 where m1.names = m2.names
and m2.color<=m1.color )as seq
from @t m1) Z
Group by names

--output
names    Output
John	Blue,Red,,
Jane	Green,Yellow,,

what if it contains more than 4 color values for single name?

use this


CREATE FUNCTION GetColorList
(
@Name varchar(100)
)
RETURNS varchar(8000) 
AS
BEGIN
DECLARE @ColorList varchar(8000)

SELECT @ColorList=COALESCE(@ColorList+',','') + Color
FROM Color
WHERE Name=@Name
RETURN @ColorList
END

then call it like this

SELECT Name,dbo.GetColors(Name) AS ColorValues
FROM People

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-31 : 21:58:16

Sorry Visakh, Not being picky over here but its just Correction

quote:

Originally posted by visakh16

quote:

Originally posted by sodeep

This works for SQL 2000

--Create Table Variable
Declare @t table
(names varchar(10),Color varchar(10))

--Prepare sample data
insert @t
Select 'John', 'Red' union all
Select 'John', 'Blue' union all
Select 'Jane', 'Green' union all
Select 'Jane', 'Yellow'

Select names,
Max(Case when seq =1 then color else '' end)+ ',' +
Max(Case when seq =2 then color else '' end)+ ',' +
Max(Case when seq =3 then color else '' end)+ ',' +
Max(Case when seq =4 then color else '' end) as List
from
(SELECT m1.names,m1.color,(Select Count(*) from @t m2 where m1.names = m2.names
and m2.color<=m1.color )as seq
from @t m1) Z
Group by names

--output
names    Output
John	Blue,Red,,
Jane	Green,Yellow,,

what if it contains more than 4 color values for single name?

use this


CREATE FUNCTION GetColorList
(
@Name varchar(100)
)
RETURNS varchar(8000) 
AS
BEGIN
DECLARE @ColorList varchar(8000)

SELECT @ColorList=COALESCE(@ColorList+',','') + Color
FROM Color
WHERE Name=@Name
RETURN @ColorList
END

then call it like this

SELECT Name,dbo.GetColorList(Name) AS ColorValues
FROM Color
Group by Name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 01:02:06

yup...thanks for the spot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources