Here's another way you can try:

SELECT PROGRAM = MAX(CASE n WHEN 1 THEN [Type] END)

,MARKET_SEGMENT = MAX(CASE n WHEN 2 THEN...

Luis Cazares (8/15/2013)

---

dwain.c (8/14/2013)

---

If you're after the one with the lowest POS, this should do it:

SELECT VENDOR_ID, ADDRESS_PHONE_NUM

FROM (

-- Your query (reformatted)

SELECT...

Jeff Moden (8/15/2013)

---

Speaking of rocks, I feel like a rock because I totally missed this script entry. Thanks for the kudos, Steven, but I can't claim this method as...

Jeff Moden (8/15/2013)

---

Thank you for the kind words and I know you meant no slight but, just to be sure, Chris and Dwain are both "heavy hitters" in my book....

ChrisM@Work (8/15/2013)

---

GPO (8/15/2013)

---

Hi Dwain

I'm enormously grateful for the code you've put up. I'll test yours and Chris's and see what I can learn from them. I'll post back my observations...

GPO (8/15/2013)

---

Hi Dwain

I'm enormously grateful for the code you've put up. I'll test yours and Chris's and see what I can learn from them. I'll post back my observations after...

GPO (8/15/2013)

---

Edit: I think I get it now! The cross joins mean that 10^5 rows are generated, and you're saying it's irrelevant that 5 meaningless columns just happen to be...

GPO (8/15/2013)

---

Awesome to have the heavy hitters on the case! Jeff, where you say:

You're not actually supposed to use any data from that. You're only supposed to use the presence...

It might be helpful to understand why you want to do this.

For example, are you trying to prevent...

You may want to also look at the third link in my signature for some other examples of using recursive CTEs.

An interesting question. Probably because I don't think I'm particularly good at interpreting other people's code.

When I must do so, I usually look at the source tables first. ...

Jeff doesn't like it when I use

SELECT $

In my tally tables. :hehe: Just having a little fun there.

BTW. I noticed you also wanted the open-ended gaps before start...

GPO (8/14/2013)

---

OK. So I'm trying to understand table value constructors and cross apply. If I do this (on a SQL 2008 machine):

SELECT *

...

You just had to call me out on...

You could run the MERGE with WHEN NOT MATCHED SOURCE DELETE inside a transaction that you ROLLBACK, capturing the $ACTION from OUTPUT in a TABLE VARIABLE (so its contents are...