Hi,

I have used the cross-tab technique to get a result set back as follows...colume names listed first....

ColName, ColOrders, ColKey, ColEmail
Niall, null, null, null
null, 3, null, null
null, null, 90, null
null, null, null, None

What is the best way to get rid of these nulls and return a single record result set e.g.

ColName, ColOrders, ColKey, ColEmail
Niall, 3, 90, None

It is prefarable that some kind of view\UDF is used.

Thanks
Niall

First,
You are trying to concatenate the results from different rows into one set. Your results are different; each line represents an end results.

Is this want you want to do?

Hi,
Thanks for the reply. Yes, that is what I need to do.

Essenitally I want to transform the rows into columns.
The original table looked like this..

Option, Attribute_Value
---------------------
Name, Niall
Orders, 3
Key, 90
Email, None

So I need to manipulate into..

ColName, ColOrders, ColKey, ColEmail
------------------------------------
Niall, 3, 90, None

Thanks
Niall

This is a suggestions and I am sure you will receive others.

Look up information on concatenation on BOOKS ONLINE to start you of.

String Concatenation Operator
The string concatenation operator is the plus sign (+). You can combine, or concatenate, two or more character strings into a single character string. You can also concatenate binary strings. This is an example of concatenation:

SELECT ('abc' + 'def')

Here is the result set:

------
abcdef

(1 row(s) affected)

or
select lastname+ FirstName from employees
where lastname is not null