I think you can use CUBE/ROLLUP for this.

SELECT CASE WHEN n1.strcol IS NULL THEN 0 ELSE 1 END +

CASE WHEN n2.strcol...

CELKO (5/16/2012)

---

>> Once again, you and your high horse ride in. <<

Gee, ANSI/ISO Standards, etc are "high horse" to you? Does that say a lot about your professionalism and expertise...

Use LIKE

SELECT ..

FROM mytable

WHERE PartNumber LIKE '[0-9][0-9][0-9][0-9][A-Za-z][0-9][0-9]'

Bit weird but works...

select

'A' as [value],

null as [x],

'B' as [value]

for xml path('test'), type

This sort of things is straightforward as a SQLCLR, something like this

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text;

public partial class UserDefinedFunctions

{

enum States { Literal, UpperNext, LowerNext...

With a minor change, the cross apply code runs a lot quicker.

Here's my test set up

SET STATISTICS TIME OFF

DECLARE @x XML

SELECT @x = '<EXPORT>

<UNIT UNIT_ID="40001" TYPE="01">

...

This should be quicker

select b.value('@UNIT_ID', 'nvarchar(100)'),

r.value('@DT', 'DATETIME'),

r.value('@V', 'decimal(10, 8) ')

from...

Try this

with cte as (

select *,

row_number() over(partition by empno,cid order by enddate desc) as rn

from temptable)

delete from cte where rn>1;

UPDATE #test

SET string = dbo.RegexReplace(dbo.RegexReplace(string, '[0-9]', '9'), '[a-zA-Z]', 'X')

FROM #test

Now standing...

Join to a numbers/tally table

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE

FROM @SPLITROW s

INNER JOIN master.dbo.spt_values t ON t.type='P'

AND t.number BETWEEN 1...

See if this helps

SELECT col1,col2,col3,

LEN(col3)-LEN(REPLACE(col3,'A','')) AS NumberOfA,

LEN(col3)-LEN(REPLACE(col3,'B','')) AS NumberOfB

FROM tbl1

Have a look at dynamic cross tabs here

http://www.sqlservercentral.com/articles/Crosstab/65048/

SELECT Sales,

SUM(CASE WHEN Till=1 THEN Amount ELSE 0 END) AS Till1,

SUM(CASE WHEN Till=2 THEN Amount ELSE...

Still not really clear what you're asking for

SELECT resp_Id, Brand, [Year], [Month], FOP = MAX(CASE WHEN [Week]=2 THEN 1 ELSE 0 END) +

...