Thanks Chris, I missed that I had messed up the result. I made some changes for performance to what I...

A slight variation to Luis's method and implemented as an inline table valued function

CREATE FUNCTION compressDuplicates(@code nvarchar(4000))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH cteTally as (

SELECT TOP(LEN(@code) - 1) ROW_NUMBER() OVER (ORDER...

Another option that is very similar to Seans

select customer, sum(PA) AS [Sum_SalesofProductA], sum(PB) AS [Sum_SalesofProductB], sum(PC) AS [Sum_SalesofProductC]

from (

select customer, SalesofProductA PA, null PB, null PC

...

twin.devil (12/10/2013)

---

As the User Define table Types are created as table-valued, so you cannot assign null to a table.

2ndly if you want to check if there is any value in...

No problem:-)

Hi

Can I put this one up 🙂

select personid, goaldate, goalstatus,

row_number() over (partition by personid, goalstatus, g order by goaldate) * GoalStatus ConsecutiveGoals

from (

select personid, goaldate, goalstatus,

row_number() over (partition by...

Hi

I think this will do what you want. Inspired by Dwain's article on Alternative unpivot method[/url]

SELECT s.SaleId, t.TaxCode, SUM(t.TaxValue), SUM(s.Price)

FROM SalesRows s

CROSS APPLY (VALUES (TaxCode, TaxValue), (ExtraTaxCode, ExtraTaxValue))...

This is much the same as the the substring option, but uses stuff

SELECT

STUFF(

STUFF(

@TableName

,1

,CHARINDEX('_',@TableName),''

) -- Remove up to first

,CHARINDEX('_',@Tablename) - 1

,9999

,''

) --Remove from second

dwain.c (11/20/2013)

That's still pretty cool even though mine wasn't the swiftest. 🙂

What I was a bit unsure about was this:

WHERE C IN (3,4) AND b.CellID <> 0;

And whether it should...

dwain.c (11/20/2013)

---

Nice follow up Micky!

I have one question though, for the 4D case. Are both queries returning the same row counts?

I was a little unsure about what I did...

I thought I'd do a performance test of mine and Dwain's on a larger test set (1,000,000 cells).

Basically the results show that while mine is quicker, Dwain's (once reusable Dynamic...

dwain.c (11/18/2013)

---

Laurence Neville (11/18/2013)

---

dwain.c - nope, I have nothing against dynamic SQL!

I have to award the prize to mickeyT's solution - it is the fastest of all the contributions.

Thanks everyone...

OK , so I think I have got there, but it has made my query real ugly

declare @cellid int = 1;

select axiscode, cellid

from (

select c.cellid,

case when c.cellid =...

Laurence Neville (11/14/2013)

---

Wow! That is pretty close. However, what I needed was this result set:

+----------+---------+

| AxisCode | CellID |

+----------+---------+

| X | 1 |

| X | 2 |

| Y | 1...

I'm not sure if I am understanding the requirements for this correctly, but would this work?

declare @cellid int = 1;

select *

from CellAxes c

inner join (

select cellid

from (select axiscode, positiononaxis from...