Turn table on it's side

[WittyD]

Hello, I have a column that I am trying to display on one row. Unfortunately the tool I'm using is limited and I don't have the ability to use sp, and the reporting tool I'm using is limited so I'm left with a SQL query only. I'm using SQL 2005.


Here is the table that I have:

ID GROUPID STATUS NAME
A38 A333 C Tom
B99 A333 * Jerry
A93 A333 * Wilson

Basically I want to return this

Tom, Jerry, Wilson


Any ideas?

 

[philhege]

Do you want all of the [STATUS NAME] field contents in one field? Do you have to return any other information from the table?

Can your query tool accept more than one statement? If not, your options are limited.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[WittyD]

I can use a subquery.

 

[WittyD]

Sorry, it's [STATUS] and [Name]. I just want to return all of the names in one row or one field.

 

[gmmastros]

Can you use a view?

Here's what I'm thinking....

1. Create a function that returns a comma delimited list of names.
2. Create a view that returns data from the function
3. Call the view from your tool.

Most tools that let you use tables also let you use views.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WittyD]

I can only use t-sql statements as a 'field' in report headers and rows. My options are limited until I bring this database inhouse sometime next year.

 

[gmmastros]

Try this...

First, create a function in your database like this...

Alter Function GetNameList
	(@GroupId VarChar(20))
Returns VarChar(8000)
AS
Begin
    Declare @Out VarChar(8000)
    Set @Out = ''

    Select @Out = @Out + Name + ','
    From   [!]YourTableName[/!]
    Where  GroupID = @GroupId
           And Name Is Not NULL
    Order By Name

    if Right(@Out, 1) = ','
        Set @Out = Left(@Out, Len(@Out)-1)

    Return @Out
End

Then create this view.

Create View [!]AnyNameYourWant[/!]
As
Select Distinct GroupID, dbo.GetNameList(GroupId) As NameList
From   [!]YourTableName[/!]

Now you should be able to use the view to return your comma list of names. The AnyNameYouWant view should behave exactly like a table for reporting purposes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WittyD]

Thanks, but unfortuantely I don't have that option. I can return the names in 3 different columns like a PIVOT command but I couldn't figure a way to specify the columns without using a SP or function.

 

[RyanEK]

Hi,

Can you do the following?

DECLARE @mystring varchar(100)
SELECT @mystring = COALESCE(@mystring + ', ', '') +  NAME FROM [Table]
select @mystring

Ryan