SELECT

nameid,

telephone1 = MAX(CASE WHEN rn = 1 THEN telephone END),

telephone2 = MAX(CASE WHEN rn = 2 THEN telephone END),

telephone3 = MAX(CASE WHEN rn = 3 THEN...

TheSQLGuru (10/4/2012)

---

1) What is the point of this (isn't PRIMARY KEY sufficient by itself and the UNIQUE redundant?):

PRIMARY KEY (ID),

UNIQUE (ID))

2) I can probably count on...

Do you have another column in your existing table to identify what type of phone number it is? Mobile/work/home etc?

Reckon so 😉

dwain.c (10/4/2012)

---

Eugene Elutin (10/4/2012)

---

ChrisM@Work (10/4/2012)

---

Eugene Elutin (10/4/2012)

---

No sort? I would suggest to add ORDER BY, otherwise order is not...

Eugene Elutin (10/4/2012)

---

ChrisM@Work (10/4/2012)

---

Eugene Elutin (10/4/2012)

---

No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed,...

PiMané (10/4/2012)

---

ChrisM@Work (10/4/2012)

---

Why, in the second query, do you join the employees table to the CTE? I can't see any reason for it - unless there are dupes on EmpID....

Eugene Elutin (10/4/2012)

---

No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may...

PiMané (10/4/2012)

---

The plans are almost the same except for...

No undocumented vapourtables, no sort:

SELECT n = a.n+b.n

FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)

CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)

😉

This has to be homework because it's very simple.

Try ROW_NUMBER() OVER (PARTITION BY something ORDER BY somethingelse)

You will need to set it up as a CTE or derived table in...

This should be close.

MERGE [DBname].[dbo].[Space] AS target

USING [DBname].[dbo].[RawData_Space] AS source

ON target.SPACE_ID = source.SPACE_ID

WHEN MATCHED THEN

...

maida_rh (10/4/2012)

---

How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.

E.g :

Select LTRating ,RHDate from Accounts...

Post the actual execution plans as .sqlplan attachments. It's difficult to determine anything useful from these figures.

deepakagarwalathome (10/3/2012)

---

Hi Chris

Thanks for that!I fully understand what you are trying to do with the CTE's. I have run the query with the CTE's.

The problem, however, remains the same, that...