Of course, if you can guarantee the maximum number of sub-rows that there are (my assumption below is 2, controlling the number of LAGs to COALESCE), you may be able...

Unfortunately the LAG function goes back a fixed...

Remarks from BOL:

If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come...

Hmmm... I was really hoping to hear back from the OP whether my suggestion helped.

I'll be a little more charitable...

Revenant (10/25/2013)

---

Luis Cazares (10/25/2013)

---

I'm losing faith in humanity here as I've been interviewing some candidates for BI developers at my job. The last one claiming to have 8 years of...

Clearly we are having way too much fun here! 😛

A bit of a different take on this, just for fun.

WITH SampleData (ID, MyString) AS

(

SELECT 1, '1:1,3,5,7,4:56,43,58,5:34,67r,234'

)

SELECT ID

,ItemNumber

,value1=CASE...

Post retracted... not enough coffee this morning.

Yet another way:

SELECT *

FROM iPhones a

CROSS APPLY

(

SELECT TOP 1 location_id, date_assigned

FROM location_history b

WHERE a.iphone_id = b.iphone_id

...

With a multi-line TVF like you have there, as Grant points out you need to define the table columns.

If you can convert it into an inline TVF (and the sample...

This WHILE LOOP might be a little cleaner and faster than what you've got and should resolve to any number of levels.

DECLARE @increment TINYINT

DECLARE @didinsert TINYINT

/* create test data for...

I guess its going to depend on how many levels you need but I believe this works on your test data:

WITH AllPersons AS

(

SELECT DISTINCT c.partykey

...

Pulivarthi Sasidhar (10/22/2013)

---

Hi,

Use the following Function....

CREATE FUNCTION fn_getrowsfrmCSVstring(@str varchar(max))

returns @rtn table (id int identity(1,1),value varchar(2000))

As

Begin

Declare

@mstr varchar(max),@i INT,@len INT

SELECT...

You probably want to do something like this then:

WITH SampleData AS

(

SELECT userid, [date], usage

FROM

(

...