also

AND CHARINDEX('%',Sessions)>0

It's not clear whether you want the keys concatenated into one column or left as separate columns.

This will concatentate the keys

SELECT a.RecordID,

(SELECT b.[Key]...

Another option is to use FOR XML PATH

SELECT m.Field1 AS Master,

STUFF((SELECT ','+c.Field1 AS "text()"

FROM Child...

Here's another way

WITH CTE AS (

SELECT SerialNumber,

ROW_NUMBER() OVER(ORDER BY SerialNumber) AS rn

FROM #yourtable)

SELECT s.SerialNumber+1 AS GapStart,

e.SerialNumber-1...

Try this

SELECT employer.statecode, COUNT(employer.statecode) AS ct

FROM employer

WHERE NOT EXISTS (SELECT *

FROM...

From what I can see, you'll need to use some dynamic SQL to select from @TBL.

Try replacing this

declare value cursor for

select report_code, max(REPORT_NO)

from @TBL

where person_code = @person_code

and...

Several ways to do this, here's one

WITH CTE AS(

SELECT Name, Factor1, Factor2,

ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Factor1) as rn

FROM MyNames)

SELECT Name, Factor1, Factor2

FROM CTE

WHERE rn=1

Assuming you're using SQL Server 2005

SELECT

a.Acct_ID,

STUFF((SELECT ','+f.FlagTitle AS "text()"

FROM Flags f

WHERE a.Acct_ID = f.Acct_ID

ORDER BY f.FlagTitle

FOR XML PATH('')),1,1,'') AS Flags

FROM Accounts a

See if this helps

WITH ReducedAvetime AS

(SELECT origdate, duration_seconds, phoneuser, calltype

FROM #avetime a

WHERE NOT EXISTS (SELECT * FROM #avetime b

...

The subquery needs to reference phoneuser

select stime.phoneuser, avg(datediff(ss, stime.origdate, etime.origdate))

from #avetime stime

inner join #avetime etime

on stime.phoneuser = etime.phoneuser

and etime.origdate =...

Jeff,

Like the article. One suggestion though (not sure if this has already been raised in the comments - too many to look through), lots of people have problems posting XML,...

Brandon,

Few things...

Your XML hasn't appeared so it's not possible to tell what you're after. You'll need to repost.

FOR XML EXPLICIT requires an ORDER BY clause to work reliably.

If you are...

Can't see the significance of 'num' here, but try this anyway

SELECT s1.num,

s1.date1,

MIN(t1.date2) AS date2,

...

Try this

create table mytable(ID_1 int, version int, Value varchar(10))

insert into mytable(ID_1,version,Value)

select 1, 1, 'value1' union all

select 1, 1, 'value2' union all

select 1, 2, 'value1' union all

select 1, 2, 'value2'...

Easier to remove a leading '/'

SELECT

FuelType = SUBSTRING(

CASE petrol WHEN 1 THEN '/petrol' ELSE '' END +

CASE diesel WHEN 1 THEN '/Diesel' ELSE '' END +

CASE E85 WHEN 1...