You're welcome. I was gladly surprised the first time I realized that I could update/delete a CTE (is mostly like views).

If you want to learn and practice, a good free resource can be Microsoft Virtual Academy.

http://www.microsoftvirtualacademy.com/

Remember to have in mind the data type that ISNULL and COALESCE return.

ISNULL will try to convert the second expression to the data type of the first expression.

COALESCE will convert...

It's no longer a free edition. For a free edition it would say Evaluation Edition

Here's a more concise way to do it. You can update the table by updating the CTE.;-)

WITH CTE AS(

SELECT *, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ltr) rn

FROM #tmp

)

UPDATE...

The data flow will insert the data. If the file doesn't exist then it will create it. In fact, when the file doesn't exist, you have to define as well...

LinksUp (11/19/2013)

---

Test, test, and test!!

There are a number of techniques to populate a test table with millions of rows...

You could create a connection and change in the Expressions property to change the ConnectionString Property to something like this:

"C:\\MyPath\\"+ (DT_STR, 50, 1252) (MONTH(GETDATE()) == 1 ? "January" : MONTH(GETDATE())...

The Import/Export Wizard creates a "real SSIS" package, which you can save and open to edit or just review it and maybe run it again.

It's a great tool for starters...

A "large amount of data" might vary depending on the infrastructure of your company.

If you right click on your database and then choose Tasks-> Import Data... you will get a...

Maybe an SSIS package would be a better option if your doing a big load.

To insert to a "local" table using a linked server, just use the insert into as...

Use a DELETE in batches.

DECLARE @i int = 1

WHILE @i > 0

BEGIN

DELETE TOP (1000000)...

Sean Lange (11/19/2013)

---

And then it would be HIGHLY advisable to change your schema that you store dates in a date column instead of numeric. It will save you countless hours...

And now correcting previous posted queries as they had syntax errors.

Select distinct IDID,

Data= REPLACE((Select distinct t1.data as [data()]

From #Unpivot t1

Where t1.IDID = t2.IDID

Order by t1.data FOR XML...

I'm just modifying Dwain's query to reduce the code by using dense_rank.

WITH SampleData (ID, Data) AS

(

SELECT 'A', 'P1' UNION ALL

SELECT 'A', 'P1' UNION ALL

SELECT 'A', 'P2' UNION ALL

SELECT 'A', 'P2'...