+1

SELECT t1.name AS list1,

t2.name AS list2,

count(distinct c2.name) AS commonColumns

FROM sys.tables t1

CROSS JOIN sys.tables t2

INNER JOIN...

If you change the data, replacing

, ('E3A22', geometry::STGeomFromText('LINESTRING(00 12, 07 12)', 0))

with

, ('E3A22', geometry::STGeomFromText('LINESTRING(06 12, 07 12)', 0))

so that...

Typically they're not. You could try splitting out a temporary table from the CTE.

IF OBJECT_ID('tempdb..#Base')...

If you don't know how many attribute there are in advance then you will have to use dynamic SQL - not possible for a view.

Not exactly sure what you want here, but try this

SELECT s.StockCode,

s.StockName,

MAX(CASE WHEN AttributeName='SpecialField1' THEN AttributeClass END)...

Here's another way using recursive CTEs

WITH Base AS (

SELECT A.ShapeID AS ShapeID_A, B.ShapeID AS ShapeID_B

FROM dbo.OverlappingShapes AS A

INNER JOIN dbo.OverlappingShapes AS B

...

WITH CTE AS (

SELECT userid,picture,

ROW_NUMBER() OVER(PARTITION BY userid ORDER BY picture) AS rn

FROM pictures)

SELECT userid,

MAX(CASE WHEN...

There's a good article about it here[/url]

Any chance you're on SQL Server 2012?

SELECT LocationID,

stamp AS startStamp,

FIRST_VALUE(stamp) OVER(PARTITION BY LocationID

...

WITH CTE AS (

SELECT ProductType,

SUM(CASE WHEN ProductRank >= 5 THEN ProductSales ELSE 0 END) AS GoodGrade,

SUM(CASE...

Maybe this?

DENSE_RANK() OVER (ORDER BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID) AS BUILDINGID,

SELECT Account Number,QTY,Name,ROWID, SUM(CONVERT(INT,QTY)) OVER(PARTITION BY AccountNo) AS UOM

FROM tfrOrderTransmissionDetailLog

WHERE DateDiff(dd, [TIMESTAMP],GetDate())=0

ORDER BY UOM DESC

WITH CTE AS (

SELECTid, oracle_id, field1, field2, field3, field4,

ROW_NUMBER() OVER(PARTITION BY field1, field2, field3 ORDER BY field4 DESC)...

Can you explain how you get the value 781?