<div>
UNION or UNPIVOT would be the way I would do it.<br />
<br />
Of course, setting up the table that way in the first place was a bad idea :)
</div>


<div>
<i>I did with a union but seems slow any thoughts?</i><br />
As suggested previously, if it is too slow consider designing the table correctly.
</div>


<div>
not possible to redesign table. Legacy system.
</div>


<div>
In your procedure, create a temp table that is designed correctly and pre-determine what goes into that table before you do any querying of the data. This narrows your result set before you start smashing your SQL Server memory with high traffic. With it being a temporary table as well it will re-direct a lot of the processing onto tempdb.
</div>


<div>
<i>not possible to redesign table. Legacy system.</i><br />
Then you are in for a rough ride.
</div>


<div>
oh I know, the funny part is they want to buy this code....
</div>


<div>
<div class="content wysiwyg-content">
can anyone tell me a better way to do this<br />
<br />
I have a table where they have <br />
<br />
client l1 l2 l3 l4 l5 l6 up to l50<br />
<br />
I need the results like<br />
<br />
client l1<br />
client l2<br />
<br />
<br />
I did with a union but seems slow any thoughts?
</div>
</div>


can anyone tell me a better way to do this

I have a table where they have 

client l1 l2 l3 l4 l5 l6 up to l50

I need the results like

client l1
client l2


I did with a union but seems slow any thoughts?

UNION or UNPIVOT would be the way I would do it.

Of course, setting up the table that way in the first place was a bad idea :)

I did with a union but seems slow any thoughts?
As suggested previously, if it is too slow consider designing the table correctly.

not possible to redesign table. Legacy system.

In your procedure, create a temp table that is designed correctly and pre-determine what goes into that table before you do any querying of the data. This narrows your result set before you start smashing your SQL Server memory with high traffic. With it being a temporary table as well it will re-direct a lot of the processing onto tempdb.

not possible to redesign table. Legacy system.
Then you are in for a rough ride.

oh I know, the funny part is they want to buy this code....

query performance

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.