You're welcome. Glad that helped.

I can help you get rid of this cursor. It seems that all you actually need is to read up on the output clause. From the original code you posted...

Select statements usually come from tables. The pictures don't do much.

julesbenone (3/22/2013)

---

Sean Lange (3/22/2013)

---

Since you just want a grid solution maybe something like this would work?

;with StateData as

(

select t1.*, ROW_NUMBER() over(partition by t1.STATE order by t1.city) as RowNum

from...

I understand the challenges here but why in the world do you have a table with SSN in them like this? This type of thing should not be in your...

Ed Wagner (3/22/2013)

---

For #1 and #3, try Jeff Moden's string splitter. http://www.sqlservercentral.com/articles/Tally+Table/72993/

For #2, Sean Lange posted this great approach earlier today for a similar request:

with EconSearch(SomeValue) as

(

select 'D.01.01.01' union...

Yes splitting your parameter is the way to go here. All of these can easily be done using Jeff's splitter. You can find it by following the link in my...

rehook2003 (3/22/2013)

---

Worked like a charm!!

I originally started with a sub query, but without success changed to the left join... I see now why it was failing.

Sean, Thanks for your help...

Since you just want a grid solution maybe something like this would work?

;with StateData as

(

select t1.*, ROW_NUMBER() over(partition by t1.STATE order by t1.city) as RowNum

from #tempCityState t1

join #tempCityState...

Make sure you don't just blindly find that function and use it. You need to read that article and understand what it is doing!!!

Cool. Sounds like you don't need the cross tab stuff at all. 😛

The following will produce the results you are looking for.

;with EconSearch(SomeValue) as

(

select 'D.01.01.01' union all

select 'D.01.01.02' union all

select...

Excellent Rick. That is exactly what we are looking for!!!

The reason your date column was null is because you did a left join but filtered the row out with the...

davdam8 (3/22/2013)

---

Thanks!

Based on this query and this example (http://www.sqlservercentral.com/Forums/Topic1416551-391-1.aspx), which only retrieves only four levels:

With MySampleData (AllLevels)

AS

(

SELECT 'Analysis»Letters»Numbers»Detail' UNION ALL

SELECT 'Description»Letters»Numbers»Detail' UNION ALL

SELECT 'Planning»Letters»Symbols»Detail'

)

SELECT...

Sam S Kolli (3/22/2013)

---

Lynn...since D is not present in the parent table, none of A, B, D should be inserted even though A and B are present in the parent...

rootfixxxer (3/22/2013)

---

Thanks

@sean-2 Lange

I know the nullif, but to use that function i need to write every column... :crazy:

@steve-2 Jones - SSC Editor

I'll create some sample data... The original table it's...