Out of curiosity I have come up with another variation using OPENROWSET BULK as the starting point

DECLARE @File VARCHAR(100)

SET @File = 'C:\Sample2.csv'

DECLARE @ROWSET NVARCHAR(255)

DECLARE @HeadersRAW VARCHAR(8000)

DECLARE @RowList VARCHAR(MAX)

DECLARE @ConvertHeaderList VARCHAR(MAX)

--First...

You sure can use a function to action a DELETE.

In this example, the function TestTempFunction calls the stored procedure TestTempProc and deletes 80 rows.

USE tempdb

GO

CREATE TABLE TestTemp...

I had already done a PIVOT when I saw you'd already got it!

;WITH ctePrepare

AS

(SELECT id, name, name + ' (' + CONVERT(VARCHAR, counts) + ')' AS NameWithCount FROM test)

SELECT...

An alternative approach is to concatenate the StartDate, EndDate, Salary details into one block and then to PIVOT the data based on this concatenated block. Then in the final SELECT...

In SQL Server 2005 etc you can avoid all the subqueries by using UNPIVOT and PIVOT as well as tapping in to the ROW_NUMBER function

IF NOT OBJECT_ID('tempdb.dbo.#Before', 'U') IS NULL

DROP...

In terms of the data you present here, the following will transpose your table to the desired format. You may see a pattern here which you could build up dynamically...

Sorry folks, can't leave this one alone. I have come up with another variation, which like Wayne's, uses the TALLY table to do both the split and the...

Yes it would. Maybe Vettori can produce something special in his 100th Test.

Doing all right so far with 4-36 and running out Ponting to boot!

Nice solution Paul. As another variation, I have adapted it slightly to a self-contained CTE block without the table function. You may recognize the final Listed...

Heck why not?

And miss out the QUADRILLION altogether!:-)

Yeh, whose idea was it to scale this to a trillion anyway?:unsure:

Yes Mister Magoo, I can certainly echo that sentiment.

It's been an interesting experience!:ermm:

THE TRILLION - CROSS APPLY Version

The CROSS APPLY variation (cf Post #882552) of THE TRILLION has edged out both multiple unpivots and the CTE version of the table join. ...

OK then, say we don't even risk putting the file into a table and run the CTE directly on the file using OPENROWSET BULK. Then in cte3 we...

Out of interest, I have come up with another variation which produces the same result as Lutz

IF NOT OBJECT_ID ('tempdb.dbo.#FOO', 'U') IS NULL

DROP TABLE #FOO

SELECT 'Table: tmpNames' AS BulkColumn...