Not sure if this is any better, but here's one way: -

SELECT

a.id1, MAX(b.d1) AS description,

a.id2, MAX(b.d2) AS description,

a.id3, MAX(b.d3) AS description,

a.id4, MAX(b.d4) AS description

FROM Table1 a

CROSS APPLY (SELECT

...

ChrisM@Work (7/25/2012)

---

Phil Parkin (7/25/2012)

---

ChrisM@Work (7/25/2012)

---

This looks very much like a homework question, in which case Anthony's reply is a good place to start.

Don't forget that TOP is meaningless without...

beeramgopi (7/25/2012)

---

DECLARE @CumCredit TABLE

(RowId INT, CustomerId INT, TransId INT, Pts INT)

INSERT INTO @CumCredit (Rowid, CustomerID, TransId, pts)

select 1,123,121,10

union

select 2,123,131,20

union

select 3,123,141,15

select * from @CumCredit

select a.*, (select sum(Pts) from @CumCredit where...

Jeff Moden (7/6/2012)

---

Cadavre (7/6/2012)

---

Or, if we know that the "Column1" is unique we could do a subquery like this: -

SELECT MAX(CASE WHEN RowNum % 3 = 0 THEN Column1 ELSE...

Jeff Moden (7/6/2012)

---

Here we go. This will do the trick in SQL Server 2000.

--===== Conditionally drop the test table to make reruns in SSMS easier.

...

Yes, laziness from me 😉

Technically,...

Sure. How's this?

SELECT pk, pcode

FROM postcode

CROSS APPLY (SELECT REPLACE(pcode COLLATE Latin1_General_BIN2,' ','')) b(fixedPcode)

/*---------------------------------*UK Postcodes are arranged like this: -

SortingOfficeCode+Space+LocalCode

e.g. SK13 8LY or M1 1AA

The LocalCode is always 3 characters...

David McKinney (7/6/2012)

---

Depends...

Corrected your DDL: -

--===== If the test tables already...

Actually, this would probably be more accurate: -

SELECT pk, pcode

FROM postcode

CROSS APPLY (SELECT REPLACE(pcode COLLATE Latin1_General_BIN2,' ',''))...

More information please!! 😀

To get the previous Sunday, you could do something like this: -

DECLARE @date AS DATETIME = '2012-07-05 00:00:00.000';

SELECT DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-08', @date), '2012-01-08'); -- Previous Sunday,...

WHERE LEN(pcode) IN (6,7) AND CHARINDEX(' ',pcode) = 0

This is probably not a good way of determining validity of post codes though, there are a lot of rules (well, there...

You haven't really given enough information to answer your question.

Here's a random guess: -

WITH t1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)), -- 2 Rows

t2(N) AS (SELECT 1 FROM t1 x, t1...

chaithu559 (7/4/2012)

---

Hi ,

Let me take 2 examples :

microsoft i am referring to : select * from emp ,dept where emp.deptno=dept.deptno

So, is there...

PATINDEX, LIKE and CHARINDEX can all be used to simulate regex.

There's an article on CLR implementations here[/url], with further discussion in the discussion section (which I still intend to go...