Probably something like this.

WITH SampleData (ID, Data) AS

(

SELECT 'A', 'P1' UNION ALL

SELECT 'A', 'P1' UNION ALL

SELECT 'A', 'P2' UNION ALL

SELECT 'A', 'P2' UNION ALL

SELECT 'A', 'P3' UNION ALL

SELECT 'A', 'P4'...

A couple of caveats to using this technique:

- All columns created will default to allow NULL values.

-...

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 ......

Old thread but this is my reference article on the subject:

Efficiently Querying and Calculating Using SQL Server Date/Time Columns --by Adam Machanic

Age is covered near the end.

A very concise and clear article Wayne.

I've read a number of others on this topic and none came across quite as clear.

Too bad I didn't have this a couple of...

ksatpute123 (11/18/2013)

---

dwain.c (11/17/2013)

---

If contact number is the only column that may have duplicates, you can also do it like this.

DECLARE @temp TABLE(id int, name varchar(50), age int, contact_number varchar(50));

insert into...

Hold on... rethinking my answer.

Jeff Moden (11/17/2013)

---

WayneS (11/17/2013)

---

Speaking of Tally / Numbers tables - does anyone have a tally-table based query that you wish would run even faster? I'm going to be doing a...

WayneS (11/17/2013)

---

Speaking of Tally / Numbers tables - does anyone have a tally-table based query that you wish would run even faster? I'm going to be doing a test of...

Tom,

Interesting problem but I can't quite wrap my head around what you're looking for. But here's an rCTE solution that may be something like it.

CREATE TABLE #coinlist (val int,...

If contact number is the only column that may have duplicates, you can also do it like this.

DECLARE @temp TABLE(id int, name varchar(50), age int, contact_number varchar(50));

insert into @temp(id,name) select...

Laurence Neville (11/15/2013)

---

dwain.c - your solution returns the right rows for 3 dimensions (X,Y,Z) and the query plan is by far the best, but it is hard-coded to assume those...

Having looked at your question again, I think I see what you're trying to do.

Not sure how well this will perform but for what it's worth, you can try this:

DECLARE...

Then how specifically does 2011-04-30 come to be an end date in the expected results?

Please ignore the error I reported for MickyT's solution. Copy/paste error.

I'm going back to my previous post to include MickyT's results.