Lowell (6/14/2012)

---

easy peasy:

CREATE SYNONYM MailView FOR msdb.dbo.sysmail_allitems

select * from MailView

* note a synonym must point to an object.

so you cannot try to make it part of a name, like...

I can add a couple of points because I'm a great believer in using CTEs to improve code readability.

1. You can also think of a CTE as a single-use VIEW....

Couple of issues here:

1) You did not post DDL nor data in readily consumable form, but as I'm feeling charitable this morning, I have done that for you.

2) Your expected...

Sean Lange (6/14/2012)

---

One requirement of a CTE is the previous line must end with a semicolon. As such many people have developed the habit of beginning a cte that way.

;with...

I would use sp_executesql too in the real world. Too lazy to post solutions to the forum in that...

I'm still wondering where CTE2 is defined.

I'm 99.99% certain that the posted SQL will generate a syntax error.

I'd suggest an answer (I know how to do this) but I'd want to put it into a syntactically...

Lowell (6/13/2012)

---

a neat trick with the PARSENAME function, which is used to chop up object names like ServerName.DatabaseName.SchemaName.ObjectName:

--Results:

/*

ServerName Instance

----------- --------

MyServer SQL2005

*/

SELECT

PARSENAME(Replace(instancename,'\','.'),2) AS ServerName,

PARSENAME(Replace(instancename,'\','.'),1) AS Instance

from(SELECT 'MyServer\SQL2005'...

Or this?

SELECT CASE WHEN SourceA IS NULL THEN 'NONE' ELSE SourceA END

FROM #Temp1

GROUP BY SourceA WITH ROLLUP

ORDER BY SourceA

My preference is the Dynamic Crosstab approach, so let me give you the briefest of tutorials on how I go about achieving a working query.

First, you need to create a...

ChrisM@Work (6/13/2012)

---

dwain.c (6/13/2012)

---

sridhar_kola (6/13/2012)

---

Hi ,

Thanks , it does give the right results .

However, just to understand the code , what does the n%2 in the Where condition signify...

This might work but it may end up being slower without some kind of indexing:

;WITH CTE AS (

SELECT Student, Grade, Apple, Pear, Banana, Age, Tag

...

Initial post deleted.

I use...

Well, it's not like Inserted.ID is going to...