You've done all the hard work. To create the VIEW you just need

EXEC('CREATE VIEW YourGeneratedView AS ' + @SQL1 + @SQL2 + @SQL3)

Making a guess at your column widths, I have created the format file SAMPLE_FMT.FMT Please note you will need to add backslash n to the row terminator since...

Is 8000 characters wide enough? 🙂 You should read Jeff Moden's article referenced above which explains the concept of the tally table very well. This is one way...

Taking as an example a 20 character EmployeeName column, we can resolve the 20 individual components using the SUBSTRING function and then use UNPIVOT to flip the data vertically. ...

We can split out the 20 individual one character components using the SUBSTRING function and CROSS APPLY to pass vlcode into these values. Using the WHERE clause, we identify only...

By cross joining gah's TABLE_RECORDS to a virtual tally table, I have arrived at the following

SELECT ID, Expr1

FROM

(

SELECT ID, CASE N WHEN 1 THEN CASE WHEN B1 = 1 THEN...

In terms of the self-determining pairs of duplicates supplied in the example dataset, this works quite nicely

WITH cte AS

(

SELECT constituent_id, first_name, last_name

FROM #dup_problem

...

You can split the string on the fly using a Tally table/CROSS APPLY approach

IF NOT OBJECT_ID('tempdb.dbo.#temp', 'U') IS NULL DROP TABLE #temp

SELECT 'em001' AS Empcode, '2,3,7' AS Date INTO #temp

UNION...

Certainly you can automate Grant's UNION query approach by using FOR XML PATH. Perhaps you can adapt the script below to suit your requirements.

DECLARE @sql VARCHAR(MAX)

SELECT @sql =...

Assuming your initial query is actually

SELECT empdate, COUNT(id), category FROM tablename GROUP BY empdate, category

try this

WITH cte (empdate, idcount, category) AS

(

SELECT empdate,

...

Watch out for gaps in tabId! Hence the use of the Row_number function.

Whoops just realised this is a SQL Server 7,2000 post. This script will only...

Wouldn't it be easier to split the string and do something like

;WITH cteTally (N) AS

(SELECT Number FROM master..spt_values WHERE Type = 'P')

SELECT DISTINCT BusNo FROM busroute

CROSS APPLY

(

...

One approach is to concatenate Occ, ThisValue, ThatValue, OtherValue into one block and then to dynamically PIVOT the data based on this concatenated block and the rows present. Then in...

I'm not sure why you don't want to adopt the VIEW approach, but you could try OPENROWSET BULK if you prefer

DSAFormat.fmt

7.0

34

1SQLCHAR050"\t"1CP_Code

2SQLCHAR050"\t"2Time

3SQLCHAR050"\t"3Total_Pageviews

4SQLCHAR050"\t"4Total_Volume_in_MB

5SQLCHAR050"\t"5Edge_Traffic_Volume_in_MB

6SQLCHAR050"\t"6Midgress_Traffic_Volume_in_MB

7SQLCHAR050"\t"7Origin_Traffic_Volume_in_MB

8SQLCHAR050"\t"8Edge_Requests

9SQLCHAR050"\t"9Midgress_Requests

10SQLCHAR050"\t"10Origin_Requests

11SQLCHAR050"\t"11Total_Download_Volume_in_MB

12SQLCHAR050"\t"12Edge_Download_Response_Volume_in_MB

13SQLCHAR050"\t"13Midgress_Download_Response_Volume_in_MB

14SQLCHAR050"\t"14Origin_Download_Response_Volume_in_MB

15SQLCHAR050"\t"15Total_Upload_Volume_in_MB

16SQLCHAR050"\t"16Edge_Upload_Request_and_Response_Volume_in_MB

17SQLCHAR050"\t"17Midgress_Upload_Request_and_Response_Volume_in_MB

18SQLCHAR050"\t"18Origin_Upload_Request_and_Response_Volume_in_MB

19SQLCHAR050"\t"19Edge_OK_Requests_200_206_210

20SQLCHAR050"\t"20Edge_304_Requests

21SQLCHAR050"\t"21Edge_Redirect_Requests_301_302

22SQLCHAR050"\t"22Edge_Permission_Requests_401_403_415

23SQLCHAR050"\t"23Edge_Server_Error_Requests_500_501_502_503_504

24SQLCHAR050"\t"24Edge_Client_Abort_Requests_000

25SQLCHAR050"\t"25Edge_Other_Requests(all_other_status_codes)

26SQLCHAR050"\t"26Edge_403_Requests

27SQLCHAR050"\t"27Edge_404_Requests

28SQLCHAR050"\t"28Origin_404_Requests

29SQLCHAR050"\t"29Origin_OK_200_206_210_Requests

30SQLCHAR050"\t"30Origin_304_Requests

31SQLCHAR050"\t"31Origin_Redirect_301_302_Requests

32SQLCHAR050"\t"32Origin_Permission_401_403_415_Requests

33SQLCHAR050"\t"33Origin_Server_Error_Requests_500_501_502_503_504

34SQLCHAR050"\r\n"34Origin_Other_Requests_(all_other_status_codes)

INSERT DSA

([CP...

You could try bulk inserting into the corresponding VIEW in which case you don't need a format file at all

CREATE VIEW VIEW_DSA

AS

SELECT

[CP Code] ,

[Time] ,

[Total Pageviews] ,

[Total Volume in MB]...