Lions Den

The Code and Times of Hanan Schwartzberg

About Hanan | Hanan's CV | Contact Hanan

December 24, 2017

How to Concatenate Rows in SQL

Filed under: SQL Server — Tags: , , — Hanan Schwartzberg @ 4:12 pm

The problem

You need to concatenate the values from a field across several rows into a single field as part of a query. For instance, if you need to pull an item’s name from one table with a list of keywords from another. At the same time any blank value need to be removed.

The Solution

SELECT I.Name,
	stuff( (SELECT (CASE IK.Keyword WHEN '' THEN NULL ELSE ',' + IK.Keyword END) 
        FROM ItemKeyword IK
        WHERE IK.ItemID = I.ItemID
        ORDER BY IK.Keyword
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
    ,1,1,'')
FROM Item I

March 16, 2009

Conditional Where in LINQ

Filed under: C#,LINQ — Tags: , , , — Hanan Schwartzberg @ 11:38 pm

Recently I needed to build a LINQ query with a conditional where clause. After a bit of searching I found a few solutions. In essence they all seem to come down to two methods. The first method is to run a second LINQ query to filter the results from the first one. The second method is to run a totally separate LINQ query for each scenario. In deciding which to use I wrote a test program that pit these two methods against each other and found that the latter was noticeably more efficient (when running the query 5000 times).

(more…)

Home | Site Design | Banner Design | Code Den | Offsite Posts | Downloads | Photography | About Hanan | Hanan's CV | Contact Hanan
Copyright © 2009 Hanan Schwartzberg. All rights reserved.