Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

condense rows to column

Status
Not open for further replies.
bartee

bartee

MIS
Mar 20, 2005
147
US
I have a query that may return a variable number of rows depending on the criteria (maybe 5, maybe 10, etc). The query only returns on column -- let's call it company name.

Instead of returning multiple rows, I would like to edit my query to return 1 row that that rolls all of the results into a comma seperated list of values.

For example (for the company name column), instead of

abc comp
xyz comp

I would like

abc comp,xyz comp


What is the most straight forward and effecient way to accomplish this?

Thank You.
 
Sort by date Sort by votes
It goes something like this:
Code: 
DECLARE @result varchar(255)
SET @result = ''

SELECT @result = @result + company_name
FROM companies
WHERE 'your conditions here'
Adjust length for @result variable as needed.

I hope this helps!

[morning]
 
Upvote 0 Downvote
My bad ...
Code: 
SELECT @result = @result + company_name + ', '
FROM companies
WHERE 'your conditions here'

SELECT @result = SUBSTRING(@result, 1, LEN(@result) - 1)

SELECT @result

[morning]
 
Upvote 0 Downvote
Joulius,

There is a trick you can use to prevent the last comma from being added to the result.

Code: 
Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ',' + company_name, company_name)
From   Companies
Where  'your condition here'
Order By company_name

Select @Result

Initially, @Result is NULL. When you add strings to null, you get null. So the IsNull check returns just the company name. For all the other records, @Result is not null, so we add ',' and the next value.

For a coding perspective, I can't really say which is better. This is simply a trick to prevent the comma from appearing at the end of the string.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
> For a coding perspective, I can't really say which is better. This is simply a trick to prevent the comma from appearing at the end of the string.

Well... this is safer against "loose" data and "lazy" coding. NULLs in data won't turn final @Result into NULL. And you don' have to initialize @Result to '' or use SET CONCAT_NULL_YIELDS_NULL or whatever.

From performance perspective, I guess it is a little bit slower than 'conventional' method (extra function call in set-based query).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Vongrunt,

I see what you mean. Unfortunately, my method suffers from the NULL problem.

Code: 
Declare @Temp Table(SomeValue VarChar(100))

Insert Into @Temp(SomeValue) Values('Vongrunt')
Insert Into @Temp(SomeValue) Values('Joulius')
Insert Into @Temp(SomeValue) Values(NULL)
Insert Into @Temp(SomeValue) Values('George')

Select * from @Temp

Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ',', '') + SomeValue
From   @Temp

Select @Result

The NULL in the 3rd record causes an incorrect output. So, then I tried...

Select @Result = IsNull(@Result + ',', '') + IsNull(SomeValue, '')
From @Temp

The result is: 'Vongrunt,Joulius,,George'

The double comma looks a little wierd and is probably not the desired result.

So, I suppose the safest thing to do is to filter the nulls before building the results.

Select @Result = IsNull(@Result + ',', '') + SomeValue
From @Temp
Where SomeValue Is Not NULL

Any thoughts?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
gmmastros said:
Unfortunately, my method suffers from the NULL problem.
Click to expand...

Wasn't your method:

Select @Result = IsNull(@Result + ',' + company_name, company_name)

... and not:

Select @Result = IsNull(@Result + ',', '') + SomeValue

?


SQLDenis said:
what if you have 50 records and 46 of them are nulls. There is no point looping thru all of those when you can eliminate them in the first place
Click to expand...

That's true. I was referring to the fact that George's method (the right one [smile]) will always work - with or without WHERE SomeValue Is Not Null. So it is generally safer against lazy devs (they often forget/avoid all that NULL stuff) and sparse NULLable data. The only price for that "implicit safety measure" is small CPU overhead per query.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Vongrunt,

You're sharp! I did change it midstream. But it works no better than any other method.

In the context of my example....

Code: 
Declare @Temp Table(SomeValue VarChar(100))

Insert Into @Temp(SomeValue) Values('Vongrunt')
Insert Into @Temp(SomeValue) Values('Joulius')
Insert Into @Temp(SomeValue) Values(NULL)
Insert Into @Temp(SomeValue) Values('George')

Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ',' + SomeValue, SomeValue)
From   @Temp

Select @Result

This results in: 'George' and misses the data from the first 2 records.

When it reaches the third record (the NULL), it evaluates @Result + ',' + SomeValue to NULL, so it uses SomeValue instead, which is also null and @Result is set to null. When it gets to the fourth record, the isnull check causes @Result to be set to SomeValue.

Of course, the Where SomeValue Is Not NULL also corrects the problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Your suggestion works great. However, I am trying to build a view based on this method.

I can't seem to declare variables in a view.

Any suggestions on how to get around this to create a View?

Thanks again.
 
Upvote 0 Downvote
You cannot declare variables within a view. As an alternative, you could create a user defined function to return the comma delimited string, and then create a stored procedure to return the info.

Code: 
Create Function GetCompanyNames
  (@FilterData Integer)
Returns VarChar(8000)
As
Begin
Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ',', '') + company_name
From   Companies
Where  Company_Name Is Not NULL
       And FilterField = @FilterData
Order By company_name

Return @Result
End

Then, you can use the UDF in a stored procedure to return the data.

Select Field, Field, dbo.GetCompanyNames(FieldToFilterOn)
From Companies


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
The only problem with your approach, gmmastros, is when there are lots of records in the table(s) from which the data is extracted:
Code: 
SELECT Field1,
       Field2,
       dbo.GetCompanyNames(FieldToFilterOn)
FROM tbl
...
If the query above returns lots of records there will be a lot of latency because for every row extracted the select from companies will executed.

[morning]
 
Upvote 0 Downvote
gmmastros said:
But it works no better than any other method.
Click to expand...
Ahh... that [dazed].

Funny thing, I always use 1st method you posted but under control - "supplied" values are always NOT NULL (either by column definition or WHERE clause). So consider most of my arguments half-baked technobabble.

Let's do it anyway... what about:

Code: 
Select @Result = coalesce(@Result+','+SomeValue, @Result, SomeValue) From @Temp

Btw. looks like SQL2005 can do that more transparently, with CREATE AGGREGATE and CLR external...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Joulius,

You are absolutely correct. The performance will be bad. I don't know any other SQL Server method for returning this data.

In my app, I have a report or 2 that uses this exact method. In the past, I used to return all the data to the front end, and combine the data in to a comma delimited string. By changing the process to the UDF method, the reports became faster.

So... sure it will be slow, but it will still probably be faster than combining the data on the front end.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
665
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
755
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
610
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
656
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top