Split Columns of Months into Columns of Weeks

Question

Post reply

Split Columns of Months into Columns of Weeks

jesse.owen

Old Hand

Points: 310
More actions
December 30, 2014 at 10:20 pm

#296401

Hi all,
1st time asking a question here (usually most things I need to know have been answered in other posts but I couldn't find anything this time).
I have a problem with a table that contains monthly data that I need to split into weekly data. The issue I have is that the months are in columns so I need to turn 1 month column into 4 or 5 weekly columns per month. So were I start with 26 columns (26 months of data in the input file), I need to end up with about 110 columns of weekly data.
Example In table:
ID Name 2014-01-01 2014-02-01 2014-03-01 etc.
123 abc 1 .8 .75
Example Out Table
ID Name 2014-01-06 2014-01-13 2014-01-20 2014-01-27 2014-02-03 2014-02-10 etc.
123 abc 1 1 1 1 .8 .8
I can easily do this for examples with 1 month per row as I have a pretty good calendar table. But as the months are in columns it is causing some headaches.
Unfortunately the data is uploaded from an Excel workbook each day and needs to be inserted into an existing table (which has limited my ability to manipulate the input data too much - but would prefer to do it in SQL at any rate). The input data is about 60,000 records per day.
I have tried using a curser and going though 1 row at a time but it got very complex and beyond my limited knowledge quickly.
Any help would be appreciated.
Thanks!
Here is an example of the input table (the real one actually has months that are -1 to + 24 months i.e. a rolling 26mth file).
I think I may have over simplified the example tables below. The real tables have columns named [F1], [F2]... etc. We never know the real months that the file contains. When the SSIS package uploads the file, the first row of the input table contains the header row from the excel workbook. It is this header row that contains the months for each of the columns. The data then starts in row 2.
IF (object_id('tempdb..#tab_MonthlyData') is not null )
DROP TABLE #tab_MonthlyData
create table #tab_MonthlyData (
YearMonth datetime,
RequestID bigint,
Name Varchar(255),
[2014-01-01] float,
[2014-02-01] float,
[2014-03-01] float,
[2014-04-01] float,
[2014-05-01] float
)
Insert into #tab_MonthlyData
( YearMonth,
RequestID,
Name,
[2014-01-01],
[2014-02-01],
[2014-03-01],
[2014-04-01],
[2014-05-01])
Select '2014-01-01',123456,'testProject1',1.0,1.0,0.8,0.75,1.0
Union all
Select '2014-02-01',765432,'testProject2',1.0,0.5,0.5,1.0,1.0
Union all
Select '2014-03-01',765432,'testProject2',1.0,1.0,0.2,0.2,0.5
Union all
Select '2014-04-01',123456,'testProject1',0,0,0.3,0.5,1.0
Union all
Select '2014-05-01',578945,'testProject3',1.0,1.0,1.0,1.0,1.0
Select * from #tab_MonthlyData
The output table would look like this:
(Note: there is no manipulation of the actual values. So if the value for January was 1, then each week in January would have a value of 1)
IF (object_id('tempdb..#tab_WeeklyData') is not null )
DROP TABLE #tab_WeeklyData
create table #tab_WeeklyData (
YearMonth datetime,
RequestID bigint,
Name Varchar(255),
[2014-01-06] float,
[2014-01-13] float,
[2014-01-20] float,
[2014-01-27] float,
[2014-02-03] float,
[2014-02-10] float,
[2014-02-17] float,
[2014-02-24] float,
[2014-03-03] float,
[2014-03-10] float,
[2014-03-17] float,
[2014-03-24] float,
[2014-03-31] float,
[2014-04-07] float,
[2014-04-14] float,
[2014-04-21] float,
[2014-04-28] float,
[2014-05-05] float,
[2014-05-12] float,
[2014-05-19] float,
[2014-05-26] float
)

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63578 More actions · Answer 1

Just throwing this out there, but what if you do something like

UNPIVOT to convert the dates in the column names to rows,

join to the Calendar table to get week numbers (and maybe years)

rePIVOT, pivoting on (Year,weekOfYear) or something like that?

If you're doing this in SSIS, you should be able to use the UNPIVOT/PIVOT transformations.

jesse.owen Old Hand Points: 310 More actions · Answer 2

Thanks.

One of the things I found when googling was the Pivot and Unpivot functions. I'm not sure if I was reading poorly written sites or it was just a bit too complex for me (i'm going with option 2)... but I couldn't work out how to use them.

The other issue is that I'm working within an existing system that I can't change very easily (the company decided not to extend the contract of the 1 dba that knew what he was doing with this stuff )

To give a bit more detail. Currently we upload a workbook that has the data in weeks. The SSIS package uploads the weekly data into a working table called InDemand. This table has a large number of columns named [F1], [F2] ....[F100]. The 1st row is the header row from the excel work book. This row contains the labels such as Request ID, Name as well as the week dates (Week starting on Monday's). Row 2 onwards contains the data.

There are then a series of stored procedures that take the data in the InDemand table and procecess it into the various raw tables, dimension tables and then makes it available in the required views.

There is also an identical excel file to the weekly file that contains the same data except in months. Where as the weekly file goes from -1 week to +24 weeks (rolling 26 week period), the monthly data goes from -1 month to + 24 months.

The powers that be want me to extend the reporting capability from +24 weeks to > 52 weeks. And they still want the weekly detail...

I have checked all the other stored procedures and I can easily modify them to cater for the increased number of weeks ... provided that I can get the data from the monthly excel file appended into the InDemand table.

Hence why I need to transform the columns for the months into weekly columns so I can insert them into the InDemand table.

Once I do that, I can update the other SP's and get > 2 years reporting.

That was a lot of text sorry! does it make sense though?

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 3

Quick and slightly hurried example of how to achieve this, mind you it's not a complete solution but should be enough to get you passed this hurdle. (Some) Comments in the code;-)

USE tempdb;

GO

SET NOCOUNT ON;

/* The sample data */

IF (object_id('dbo.tab_MonthlyData') is not null )

DROP TABLE dbo.tab_MonthlyData

create table dbo.tab_MonthlyData (

YearMonth datetime,

RequestID bigint,

Name Varchar(255),

[2014-01-01] float,

[2014-02-01] float,

[2014-03-01] float,

[2014-04-01] float,

[2014-05-01] float

)

Insert into dbo.tab_MonthlyData

(YearMonth,

RequestID,

Name,

[2014-01-01],

[2014-02-01],

[2014-03-01],

[2014-04-01],

[2014-05-01])

Select '2014-01-01',123456,'testProject1',1.0,1.0,0.8,0.75,1.0

Union all

Select '2014-02-01',765432,'testProject2',1.0,0.5,0.5,1.0,1.0

Union all

Select '2014-03-01',765432,'testProject2',1.0,1.0,0.2,0.2,0.5

Union all

Select '2014-04-01',123456,'testProject1',0,0,0.3,0.5,1.0

Union all

Select '2014-05-01',578945,'testProject3',1.0,1.0,1.0,1.0,1.0

;

GO

/* Calendar table config */

DECLARE @CAL_START DATE = '2014-01-01';

DECLARE @ZERO_DATE DATE = '1900-01-01';

DECLARE @CAL_END DATE = '2014-12-31';

/* Inline simple calendar table for POC */

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

/* This calendar has one row for each week starting

Mondays

*/

,CAL_CONFIG(TD,SD,WD) AS

(

SELECT

(DATEDIFF(DAY,@CAL_START,@CAL_END) + 7) AS TD

,@CAL_START AS SD

,DATEADD(DAY,7 - (DATEDIFF(DAY,@ZERO_DATE,@CAL_START) % 7),@CAL_START) AS WD

)

,NUMS(N) AS (SELECT TOP((SELECT TD / 7 FROM CAL_CONFIG)) ROW_NUMBER() OVER

(ORDER BY (SELECT NULL)) -1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

,BASE_CAL AS

(

SELECT

NM.N

,DATEADD(WEEK,NM.N,CC.SD) AS WFD

FROM NUMS NM

OUTER APPLY CAL_CONFIG CC

)

,SIMPLE_CAL AS

(

SELECT

BC.N

,BC.WFD

,(DATEDIFF(DAY,@ZERO_DATE,BC.WFD) / 7) AS WEEK_NO

,(YEAR(BC.WFD) * 100) + MONTH(BC.WFD) AS WMISO

,YEAR(BC.WFD) AS WYEAR

,MONTH(BC.WFD) AS WMONTH

FROM BASE_CAL BC

)

/* UNPIVOT using FOR XML */

,BASE_DATA AS

(

SELECT

MD.RequestID

,MD.Name

,MD.YearMonth

,( /* This sub-query creates an XML for each row

where the attribute names are the column

names from the table

*/

SELECT

*

FROM dbo.tab_MonthlyData SMD

WHERE SMD.Name = MD.Name

AND SMD.RequestID = MD.RequestID

AND SMD.YearMonth = MD.YearMonth

FOR XML AUTO, TYPE) AS MXML

FROM dbo.tab_MonthlyData MD

)

,BASE_COLS AS

(

SELECT

BD.RequestID AS RequestID

,BD.YearMonth AS YearMonth

,BD.Name AS Name

/* The "date" column names start with a digit which xml replaces with

the hexadecimal value of the digit. Since we are unlikely dealing

with anything greater than the 2000 range, we can just do a straight

replace

*/

,CONVERT(DATE,REPLACE(BASE.DATA.value('local-name(.)','VARCHAR(25)'),'_x0032_','2'),127) AS WKDAY

,BASE.DATA.value('.','FLOAT') AS WKVALUE

FROM BASE_DATA BD

/* The nodes function is used here with a wildcard to fetch all

attributes within the XML

*/

OUTER APPLY BD.MXML.nodes('//*/@*') AS BASE(DATA)

WHERE BASE.DATA.value('local-name(.)','VARCHAR(25)') NOT IN ('YearMonth','RequestID','Name')

)

,COLUMNAR_DATA AS

(

SELECT

BC.YearMonth AS YearMonth

,BC.RequestID AS RequestID

,BC.Name AS Name

,(YEAR(BC.WKDAY) * 100) + MONTH(BC.WKDAY) AS WMISO

,BC.WKDAY

,BC.WKVALUE

FROM BASE_COLS BC

)

/* Header section */

SELECT

NULL AS YearMonth

,NULL AS RequestID

,NULL AS Name

,MAX(CASE WHEN SC.N = 0 THEN QUOTENAME(SC.WFD) END) AS COL00

,MAX(CASE WHEN SC.N = 1 THEN QUOTENAME(SC.WFD) END) AS COL01

,MAX(CASE WHEN SC.N = 2 THEN QUOTENAME(SC.WFD) END) AS COL02

,MAX(CASE WHEN SC.N = 3 THEN QUOTENAME(SC.WFD) END) AS COL03

,MAX(CASE WHEN SC.N = 4 THEN QUOTENAME(SC.WFD) END) AS COL04

,MAX(CASE WHEN SC.N = 5 THEN QUOTENAME(SC.WFD) END) AS COL05

,MAX(CASE WHEN SC.N = 6 THEN QUOTENAME(SC.WFD) END) AS COL06

,MAX(CASE WHEN SC.N = 7 THEN QUOTENAME(SC.WFD) END) AS COL07

,MAX(CASE WHEN SC.N = 8 THEN QUOTENAME(SC.WFD) END) AS COL08

,MAX(CASE WHEN SC.N = 9 THEN QUOTENAME(SC.WFD) END) AS COL09

,MAX(CASE WHEN SC.N = 10 THEN QUOTENAME(SC.WFD) END) AS COL10

,MAX(CASE WHEN SC.N = 11 THEN QUOTENAME(SC.WFD) END) AS COL11

,MAX(CASE WHEN SC.N = 12 THEN QUOTENAME(SC.WFD) END) AS COL12

,MAX(CASE WHEN SC.N = 13 THEN QUOTENAME(SC.WFD) END) AS COL13

,MAX(CASE WHEN SC.N = 14 THEN QUOTENAME(SC.WFD) END) AS COL14

,MAX(CASE WHEN SC.N = 15 THEN QUOTENAME(SC.WFD) END) AS COL15

,MAX(CASE WHEN SC.N = 16 THEN QUOTENAME(SC.WFD) END) AS COL16

,MAX(CASE WHEN SC.N = 17 THEN QUOTENAME(SC.WFD) END) AS COL17

,MAX(CASE WHEN SC.N = 18 THEN QUOTENAME(SC.WFD) END) AS COL18

,MAX(CASE WHEN SC.N = 19 THEN QUOTENAME(SC.WFD) END) AS COL19

,MAX(CASE WHEN SC.N = 20 THEN QUOTENAME(SC.WFD) END) AS COL20

,MAX(CASE WHEN SC.N = 21 THEN QUOTENAME(SC.WFD) END) AS COL21

,MAX(CASE WHEN SC.N = 22 THEN QUOTENAME(SC.WFD) END) AS COL22

,MAX(CASE WHEN SC.N = 23 THEN QUOTENAME(SC.WFD) END) AS COL23

,MAX(CASE WHEN SC.N = 24 THEN QUOTENAME(SC.WFD) END) AS COL24

,MAX(CASE WHEN SC.N = 25 THEN QUOTENAME(SC.WFD) END) AS COL25

,MAX(CASE WHEN SC.N = 26 THEN QUOTENAME(SC.WFD) END) AS COL26

,MAX(CASE WHEN SC.N = 27 THEN QUOTENAME(SC.WFD) END) AS COL27

,MAX(CASE WHEN SC.N = 28 THEN QUOTENAME(SC.WFD) END) AS COL28

,MAX(CASE WHEN SC.N = 29 THEN QUOTENAME(SC.WFD) END) AS COL29

,MAX(CASE WHEN SC.N = 30 THEN QUOTENAME(SC.WFD) END) AS COL30

,MAX(CASE WHEN SC.N = 31 THEN QUOTENAME(SC.WFD) END) AS COL31

,MAX(CASE WHEN SC.N = 32 THEN QUOTENAME(SC.WFD) END) AS COL32

,MAX(CASE WHEN SC.N = 33 THEN QUOTENAME(SC.WFD) END) AS COL33

,MAX(CASE WHEN SC.N = 34 THEN QUOTENAME(SC.WFD) END) AS COL34

,MAX(CASE WHEN SC.N = 35 THEN QUOTENAME(SC.WFD) END) AS COL35

,MAX(CASE WHEN SC.N = 36 THEN QUOTENAME(SC.WFD) END) AS COL36

,MAX(CASE WHEN SC.N = 37 THEN QUOTENAME(SC.WFD) END) AS COL37

,MAX(CASE WHEN SC.N = 38 THEN QUOTENAME(SC.WFD) END) AS COL38

,MAX(CASE WHEN SC.N = 39 THEN QUOTENAME(SC.WFD) END) AS COL39

,MAX(CASE WHEN SC.N = 40 THEN QUOTENAME(SC.WFD) END) AS COL40

,MAX(CASE WHEN SC.N = 41 THEN QUOTENAME(SC.WFD) END) AS COL41

,MAX(CASE WHEN SC.N = 42 THEN QUOTENAME(SC.WFD) END) AS COL42

,MAX(CASE WHEN SC.N = 43 THEN QUOTENAME(SC.WFD) END) AS COL43

,MAX(CASE WHEN SC.N = 44 THEN QUOTENAME(SC.WFD) END) AS COL44

,MAX(CASE WHEN SC.N = 45 THEN QUOTENAME(SC.WFD) END) AS COL45

,MAX(CASE WHEN SC.N = 46 THEN QUOTENAME(SC.WFD) END) AS COL46

,MAX(CASE WHEN SC.N = 47 THEN QUOTENAME(SC.WFD) END) AS COL47

,MAX(CASE WHEN SC.N = 48 THEN QUOTENAME(SC.WFD) END) AS COL48

,MAX(CASE WHEN SC.N = 49 THEN QUOTENAME(SC.WFD) END) AS COL49

,MAX(CASE WHEN SC.N = 50 THEN QUOTENAME(SC.WFD) END) AS COL50

,MAX(CASE WHEN SC.N = 51 THEN QUOTENAME(SC.WFD) END) AS COL51

,MAX(CASE WHEN SC.N = 52 THEN QUOTENAME(SC.WFD) END) AS COL52

,MAX(CASE WHEN SC.N = 53 THEN QUOTENAME(SC.WFD) END) AS COL53

FROM SIMPLE_CAL SC

UNION ALL

/* Data section

Simple cross-tab

*/

SELECT

CD.YearMonth

,CD.RequestID

,CD.Name

,MAX(CASE WHEN SC.N = 0 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL00

,MAX(CASE WHEN SC.N = 1 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL01

,MAX(CASE WHEN SC.N = 2 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL02

,MAX(CASE WHEN SC.N = 3 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL03

,MAX(CASE WHEN SC.N = 4 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL04

,MAX(CASE WHEN SC.N = 5 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL05

,MAX(CASE WHEN SC.N = 6 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL06

,MAX(CASE WHEN SC.N = 7 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL07

,MAX(CASE WHEN SC.N = 8 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL08

,MAX(CASE WHEN SC.N = 9 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL09

,MAX(CASE WHEN SC.N = 10 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL10

,MAX(CASE WHEN SC.N = 11 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL11

,MAX(CASE WHEN SC.N = 12 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL12

,MAX(CASE WHEN SC.N = 13 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL13

,MAX(CASE WHEN SC.N = 14 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL14

,MAX(CASE WHEN SC.N = 15 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL15

,MAX(CASE WHEN SC.N = 16 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL16

,MAX(CASE WHEN SC.N = 17 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL17

,MAX(CASE WHEN SC.N = 18 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL18

,MAX(CASE WHEN SC.N = 19 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL19

,MAX(CASE WHEN SC.N = 20 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL20

,MAX(CASE WHEN SC.N = 21 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL21

,MAX(CASE WHEN SC.N = 22 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL22

,MAX(CASE WHEN SC.N = 23 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL23

,MAX(CASE WHEN SC.N = 24 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL24

,MAX(CASE WHEN SC.N = 25 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL25

,MAX(CASE WHEN SC.N = 26 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL26

,MAX(CASE WHEN SC.N = 27 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL27

,MAX(CASE WHEN SC.N = 28 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL28

,MAX(CASE WHEN SC.N = 29 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL29

,MAX(CASE WHEN SC.N = 30 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL30

,MAX(CASE WHEN SC.N = 31 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL31

,MAX(CASE WHEN SC.N = 32 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL32

,MAX(CASE WHEN SC.N = 33 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL33

,MAX(CASE WHEN SC.N = 34 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL34

,MAX(CASE WHEN SC.N = 35 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL35

,MAX(CASE WHEN SC.N = 36 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL36

,MAX(CASE WHEN SC.N = 37 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL37

,MAX(CASE WHEN SC.N = 38 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL38

,MAX(CASE WHEN SC.N = 39 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL39

,MAX(CASE WHEN SC.N = 40 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL40

,MAX(CASE WHEN SC.N = 41 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL41

,MAX(CASE WHEN SC.N = 42 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL42

,MAX(CASE WHEN SC.N = 43 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL43

,MAX(CASE WHEN SC.N = 44 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL44

,MAX(CASE WHEN SC.N = 45 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL45

,MAX(CASE WHEN SC.N = 46 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL46

,MAX(CASE WHEN SC.N = 47 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL47

,MAX(CASE WHEN SC.N = 48 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL48

,MAX(CASE WHEN SC.N = 49 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL49

,MAX(CASE WHEN SC.N = 50 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL50

,MAX(CASE WHEN SC.N = 51 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL51

,MAX(CASE WHEN SC.N = 52 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL52

,MAX(CASE WHEN SC.N = 53 THEN CONVERT(NVARCHAR(20),CD.WKVALUE,0) END) AS COL53

FROM SIMPLE_CAL SC

LEFT JOIN COLUMNAR_DATA CD

ON CD.WMISO = SC.WMISO

WHERE CD.YearMonth IS NOT NULL

GROUP BY CD.YearMonth

,CD.RequestID

,CD.Name

;

Results

YearMonth RequestID Name COL00 COL01 COL02 COL03 COL04 COL05 COL06 COL07 COL08 COL09 COL10 COL11 COL12 COL13 COL14 COL15 COL16 COL17 COL18 COL19 COL20 COL21 COL22 COL23 COL24 COL25 COL26 COL27 COL28 COL29 COL30 COL31 COL32 COL33 COL34 COL35 COL36 COL37 COL38 COL39 COL40 COL41 COL42 COL43 COL44 COL45 COL46 COL47 COL48 COL49 COL50 COL51 COL52 COL53

----------------------- -------------------- ------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------

NULL NULL NULL [2014-01-01] [2014-01-08] [2014-01-15] [2014-01-22] [2014-01-29] [2014-02-05] [2014-02-12] [2014-02-19] [2014-02-26] [2014-03-05] [2014-03-12] [2014-03-19] [2014-03-26] [2014-04-02] [2014-04-09] [2014-04-16] [2014-04-23] [2014-04-30] [2014-05-07] [2014-05-14] [2014-05-21] [2014-05-28] [2014-06-04] [2014-06-11] [2014-06-18] [2014-06-25] [2014-07-02] [2014-07-09] [2014-07-16] [2014-07-23] [2014-07-30] [2014-08-06] [2014-08-13] [2014-08-20] [2014-08-27] [2014-09-03] [2014-09-10] [2014-09-17] [2014-09-24] [2014-10-01] [2014-10-08] [2014-10-15] [2014-10-22] [2014-10-29] [2014-11-05] [2014-11-12] [2014-11-19] [2014-11-26] [2014-12-03] [2014-12-10] [2014-12-17] [2014-12-24] [2014-12-31] NULL

2014-03-01 00:00:00.000 765432 testProject2 1 1 1 1 1 1 1 1 1 0.2 0.2 0.2 0.2 0.2 0.2 0.2 0.2 0.2 0.5 0.5 0.5 0.5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2014-01-01 00:00:00.000 123456 testProject1 1 1 1 1 1 1 1 1 1 0.8 0.8 0.8 0.8 0.75 0.75 0.75 0.75 0.75 1 1 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2014-05-01 00:00:00.000 578945 testProject3 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2014-02-01 00:00:00.000 765432 testProject2 1 1 1 1 1 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 1 1 1 1 1 1 1 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2014-04-01 00:00:00.000 123456 testProject1 0 0 0 0 0 0 0 0 0 0.3 0.3 0.3 0.3 0.5 0.5 0.5 0.5 0.5 1 1 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

Jeff Moden SSC Guru Points: 1004600 More actions · Answer 4

@jesse.owen,

I'm totally confused as to what the purpose of the YearMonth column in the #tab_MonthlyData table is. If you have columnar month data, why is each row associated with a YearMonth?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 5

Jeff Moden (12/31/2014)
@jesse.owen,
I'm totally confused as to what the purpose of the YearMonth column in the #tab_MonthlyData table is. If you have columnar month data, why is each row associated with a YearMonth?

Good question which I meant to ask, took it as any arbitrary data.

jesse.owen Old Hand Points: 310 More actions · Answer 6

Thanks for the reply. Happy New Year btw.

I have been looking at the solution you provided for a few days over the break. The output you get is almost perfect. I think the problem is the sample data/tables I provided. In trying to simplify the issue I made the input tables have dates as column names instead of the generic F46..F75 names with the dates being in row 1.

I think this is causing a problem in the XML part of your solution. I have tried to figure that part out for a while but ... sorry no luck.

I have created a new sample code script that incorporates your solution. You can see at the bottom of the output that the output table is correct with the columns names (F1 to F100) and the column labels and dates by weeks is correctly output in row 1. Afterwards I will add the additional columns for F100 to F250 to cater for all of the weeks in the data period.

(* I do realise as a last minute review that I don't have the ID column in the output. I don't think this is a big issue I hope.)

The issue I am having is I get an error ("Error converting data type nvarchar to float.") when it runs and there is no data output to rows 2 onwards.

I'm not sure if my assumption that the error is coming from the XML part or if it is occurring due to the NULL's in the data (although I did try it after replacing all the NULL's with '0.0' and received the same error).

Would you be able to have another quick look at this for me please. The modified code (yours and mine) is below.

Thank you again!

USE [SDMO_Temp]

GO

-- Create Sample Input table (Uploaded from Excel)

if not exists ( Select * from sys.tables where name = 'InDemand_Month')

CREATE TABLE [dbo].[InDemand_Month](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[F1] [nvarchar](max) NULL,

[F2] [nvarchar](max) NULL,

[F3] [nvarchar](max) NULL,

[F4] [nvarchar](max) NULL,

[F5] [nvarchar](max) NULL,

[F6] [nvarchar](max) NULL,

[F7] [nvarchar](max) NULL,

[F8] [nvarchar](max) NULL,

[F9] [nvarchar](max) NULL,

[F10] [nvarchar](max) NULL,

[F11] [nvarchar](max) NULL,

[F12] [nvarchar](max) NULL,

[F13] [nvarchar](max) NULL,

[F14] [nvarchar](max) NULL,

[F15] [nvarchar](max) NULL,

[F16] [nvarchar](max) NULL,

[F17] [nvarchar](max) NULL,

[F18] [nvarchar](max) NULL,

[F19] [nvarchar](max) NULL,

[F20] [nvarchar](max) NULL,

[F21] [nvarchar](max) NULL,

[F22] [nvarchar](max) NULL,

[F23] [nvarchar](max) NULL,

[F24] [nvarchar](max) NULL,

[F25] [nvarchar](max) NULL,

[F26] [nvarchar](max) NULL,

[F27] [nvarchar](max) NULL,

[F28] [nvarchar](max) NULL,

[F29] [nvarchar](max) NULL,

[F30] [nvarchar](max) NULL,

[F31] [nvarchar](max) NULL,

[F32] [nvarchar](max) NULL,

[F33] [nvarchar](max) NULL,

[F34] [nvarchar](max) NULL,

[F35] [nvarchar](max) NULL,

[F36] [nvarchar](max) NULL,

[F37] [nvarchar](max) NULL,

[F38] [nvarchar](max) NULL,

[F39] [nvarchar](max) NULL,

[F40] [nvarchar](max) NULL,

[F41] [nvarchar](max) NULL,

[F42] [nvarchar](max) NULL,

[F43] [nvarchar](max) NULL,

[F44] [nvarchar](max) NULL,

[F45] [nvarchar](max) NULL,

[F46] [nvarchar](max) NULL,

[F47] [nvarchar](max) NULL,

[F48] [nvarchar](max) NULL,

[F49] [nvarchar](max) NULL,

[F50] [nvarchar](max) NULL,

[F51] [nvarchar](max) NULL,

[F52] [nvarchar](max) NULL,

[F53] [nvarchar](max) NULL,

[F54] [nvarchar](max) NULL,

[F55] [nvarchar](max) NULL,

[F56] [nvarchar](max) NULL,

[F57] [nvarchar](max) NULL,

[F58] [nvarchar](max) NULL,

[F59] [nvarchar](max) NULL,

[F60] [nvarchar](max) NULL,

[F61] [nvarchar](max) NULL,

[F62] [nvarchar](max) NULL,

[F63] [nvarchar](max) NULL,

[F64] [nvarchar](max) NULL,

[F65] [nvarchar](max) NULL,

[F66] [nvarchar](max) NULL,

[F67] [nvarchar](max) NULL,

[F68] [nvarchar](max) NULL,

[F69] [nvarchar](max) NULL,

[F70] [nvarchar](max) NULL,

[F71] [nvarchar](max) NULL,

[F72] [nvarchar](max) NULL,

[F73] [nvarchar](max) NULL,

[F74] [nvarchar](max) NULL,

[F75] [nvarchar](max) NULL,

[F76] [nvarchar](max) NULL,

[F77] [nvarchar](max) NULL,

[F78] [nvarchar](max) NULL,

[F79] [nvarchar](max) NULL,

[F80] [nvarchar](max) NULL,

[F81] [nvarchar](max) NULL,

[F82] [nvarchar](max) NULL,

[F83] [nvarchar](max) NULL,

[F84] [nvarchar](max) NULL,

[F85] [nvarchar](max) NULL,

[F86] [nvarchar](max) NULL,

[F87] [nvarchar](max) NULL,

[F88] [nvarchar](max) NULL,

[F89] [nvarchar](max) NULL,

[F90] [nvarchar](max) NULL,

[F91] [nvarchar](max) NULL,

[F92] [nvarchar](max) NULL,

[F93] [nvarchar](max) NULL,

[F94] [nvarchar](max) NULL,

[F95] [nvarchar](max) NULL,

[F96] [nvarchar](max) NULL,

[F97] [nvarchar](max) NULL,

[F98] [nvarchar](max) NULL,

[F99] [nvarchar](max) NULL,

[F100] [nvarchar](max) NULL,

CONSTRAINT [PK_InDemand_Month] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****************************************************

* Add the first row to the sample data *

* This is a header / label row *

****************************************************/

truncate table [dbo].[InDemand_Month]

INSERT INTO [dbo].[InDemand_Month]

([F1]

,[F2]

,[F3]

,[F4]

,[F5]

,[F6]

,[F7]

,[F8]

,[F9]

,[F10]

,[F11]

,[F12]

,[F13]

,[F14]

,[F15]

,[F16]

,[F17]

,[F18]

,[F19]

,[F20]

,[F21]

,[F22]

,[F23]

,[F24]

,[F25]

,[F26]

,[F27]

,[F28]

,[F29]

,[F30]

,[F31]

,[F32]

,[F33]

,[F34]

,[F35]

,[F36]

,[F37]

,[F38]

,[F39]

,[F40]

,[F41]

,[F42]

,[F43]

,[F44]

,[F45]

)

Values (

'[Col Name 1]',

'[Col Name 2]',

'[Col Name 3]',

'[Col Name 4]',

'[Col Name 5]',

'[Col Name 6]',

'[Col Name 7]',

'[Col Name 8]',

'[Col Name 9]',

'[Col Name 10]',

'[Col Name 11]',

'[Col Name 12]',

'[Col Name 13]',

'[Col Name 14]',

'[Col Name 15]',

'[Col Name 16]',

'[Col Name 17]',

'[Col Name 18]',

'[Col Name 19]',

'[Col Name 20]',

'[Col Name 21]',

'[Col Name 22]',

'[Col Name 23]',

'[Col Name 24]',

'[Col Name 25]',

'[Col Name 26]',

'[Col Name 27]',

'[Col Name 28]',

'[Col Name 29]',

'[Col Name 30]',

'[Col Name 31]',

'[Col Name 32]',

'[Col Name 33]',

'[Col Name 34]',

'[Col Name 35]',

'[Col Name 36]',

'[Col Name 37]',

'[Col Name 38]',

'[Col Name 39]',

'[Col Name 40]',

'[Col Name 41]',

'[Col Name 42]',

'[Col Name 43]',

'[Col Name 44]',

'[Col Name 45]'

)

/************************************************

* Add in the month label names to the 1st row. *

* This changes each month as the excel input *

* file is a rolling -4 to + 22 month file *

* i.e. these dates come from the excel. I'm just creating test data here. *

************************************************/

Declare @sql varchar(255)

Declare @i int = 46

Declare @month int

Set @month = 8

Declare @year int

Set @year = 2014

While @i <= 75

begin

Set @sql = '

UPDATE [dbo].[InDemand_Month]

SET [F' + cast(@i as Varchar) +'] = ''' + Cast(@year as varchar) + '-' + Right('00' + cast(@month as varchar),2) + '-01'''

+ 'WHERE [InDemand_Month].[F1] = ''[Col Name 1]'''

-- Debug

--Select @sql

exec(@sql)

Set @i = @i + 1

if @month = 12

begin

set @month = 1

Set @year = @year + 1

end

else

begin

set @month = @month + 1

end

/****************************************************

* Add the sample rows of data *

* All columns from F1 to F44 are just arbitary *

* for this issue. They contain various project *

* details. *

* No combination of these columns make any row *

* unique. Only the ID column is unique. *

****************************************************/

INSERT INTO [dbo].[InDemand_Month]

([F1]

,[F2]

,[F3]

,[F4]

,[F5]

,[F6]

,[F7]

,[F8]

,[F9]

,[F10]

,[F11]

,[F12]

,[F13]

,[F14]

,[F15]

,[F16]

,[F17]

,[F18]

,[F19]

,[F20]

,[F21]

,[F22]

,[F23]

,[F24]

,[F25]

,[F26]

,[F27]

,[F28]

,[F29]

,[F30]

,[F31]

,[F32]

,[F33]

,[F34]

,[F35]

,[F36]

,[F37]

,[F38]

,[F39]

,[F40]

,[F41]

,[F42]

,[F43]

,[F44]

,[F45]

,[F46]

,[F47]

,[F48]

,[F49]

,[F50]

,[F51]

,[F52]

,[F53]

,[F54]

,[F55]

,[F56]

,[F57]

,[F58]

,[F59]

,[F60]

,[F61]

,[F62]

,[F63]

,[F64]

,[F65]

,[F66]

,[F67]

,[F68]

,[F69]

,[F70]

,[F71]

,[F72]

,[F73]

,[F74]

,[F75]

)

Values

('abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','1','1','1','1','1','1','1','1','1','1','1','1','1','0.8','0.8','0.8','0.5','0.6','0.2','0.2','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL'),

('abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','1','1','1','1','1','1','1','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL'),

('abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','NULL','NULL','1','1','1','1','0.8','0.4','0.4','0.2','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL'),

('abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1'),

('abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','1','0.8','0.6','1','1','0','0','0.4','1','1','1','1','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL')

-- Debug

Select * from [InDemand_Month]

/*****************************************

* Start of Eirikur Eiriksson's code *

* I've tried to change it as best I *

* could to fit the example tables. *

* I don't fully understand some of the *

* the SQL unfortunately :( *

*****************************************/

DECLARE @CAL_START DATE

Set @CAL_START = (Select [F46] from [InDemand_Month] where [F1] = '[Col Name 1]') --'2014-01-01';

DECLARE @ZERO_DATE DATE

Set @ZERO_DATE = '1900-01-01';

DECLARE @CAL_END DATE

set @CAL_END = (Select [F75] from [InDemand_Month] where [F1] = '[Col Name 1]') --'2014-12-31';

Select @CAL_START, @CAL_END

/* Inline simple calendar table for POC */

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

/* This calendar has one row for each week starting

Mondays

*/

,CAL_CONFIG(TD,SD,WD) AS

(

SELECT

(DATEDIFF(DAY,@CAL_START,@CAL_END) + 7) AS TD

,@CAL_START AS SD

,DATEADD(DAY,7 - (DATEDIFF(DAY,@ZERO_DATE,@CAL_START) % 7),@CAL_START) AS WD

)

,NUMS(N) AS (SELECT TOP((SELECT TD / 7 FROM CAL_CONFIG)) ROW_NUMBER() OVER

(ORDER BY (SELECT NULL)) -1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

,BASE_CAL AS

(

SELECT

NM.N

,DATEADD(WEEK,NM.N,CC.SD) AS WFD

FROM NUMS NM

OUTER APPLY CAL_CONFIG CC

)

,SIMPLE_CAL AS

(

SELECT

BC.N

,BC.WFD

,(DATEDIFF(DAY,@ZERO_DATE,BC.WFD) / 7) AS WEEK_NO

,(YEAR(BC.WFD) * 100) + MONTH(BC.WFD) AS WMISO

,YEAR(BC.WFD) AS WYEAR

,MONTH(BC.WFD) AS WMONTH

FROM BASE_CAL BC

)

/* UNPIVOT using FOR XML */

,BASE_DATA AS

(

SELECT

MD.[F1]

,MD.[F2]

,MD.[F3]

,MD.[F4]

,MD.[F5]

,MD.[F6]

,MD.[F7]

,MD.[F8]

,MD.[F9]

,MD.[F10]

,MD.[F11]

,MD.[F12]

,MD.[F13]

,MD.[F14]

,MD.[F15]

,MD.[F16]

,MD.[F17]

,MD.[F18]

,MD.[F19]

,MD.[F20]

,MD.[F21]

,MD.[F22]

,MD.[F23]

,MD.[F24]

,MD.[F25]

,MD.[F26]

,MD.[F27]

,MD.[F28]

,MD.[F29]

,MD.[F30]

,MD.[F31]

,MD.[F32]

,MD.[F33]

,MD.[F34]

,MD.[F35]

,MD.[F36]

,MD.[F37]

,MD.[F38]

,MD.[F39]

,MD.[F40]

,MD.[F41]

,MD.[F42]

,MD.[F43]

,MD.[F44]

,MD.[F45]

-- MD.RequestID

--,MD.Name

--,MD.YearMonth

,( /* This sub-query creates an XML for each row

where the attribute names are the column

names from the table

*/

SELECT

*

FROM [InDemand_Month] SMD

WHERE SMD.[ID] = MD.[ID]

/*

SMD.[F1] = MD.[F1]

AND SMD.[F2] = MD.[F2]

AND SMD.[F3] = MD.[F3]

AND SMD.[F4] = MD.[F4]

AND SMD.[F5] = MD.[F5]

AND SMD.[F6] = MD.[F6]

AND SMD.[F7] = MD.[F7]

AND SMD.[F8] = MD.[F8]

AND SMD.[F9] = MD.[F9]

AND SMD.[F10] = MD.[F10]

AND SMD.[F11] = MD.[F11]

AND SMD.[F12] = MD.[F12]

AND SMD.[F13] = MD.[F13]

AND SMD.[F14] = MD.[F14]

AND SMD.[F15] = MD.[F15]

AND SMD.[F16] = MD.[F16]

AND SMD.[F17] = MD.[F17]

AND SMD.[F18] = MD.[F18]

AND SMD.[F19] = MD.[F19]

AND SMD.[F20] = MD.[F20]

AND SMD.[F21] = MD.[F21]

AND SMD.[F22] = MD.[F22]

AND SMD.[F23] = MD.[F23]

AND SMD.[F24] = MD.[F24]

AND SMD.[F25] = MD.[F25]

AND SMD.[F26] = MD.[F26]

AND SMD.[F27] = MD.[F27]

AND SMD.[F28] = MD.[F28]

AND SMD.[F29] = MD.[F29]

AND SMD.[F30] = MD.[F30]

AND SMD.[F31] = MD.[F31]

AND SMD.[F32] = MD.[F32]

AND SMD.[F33] = MD.[F33]

AND SMD.[F34] = MD.[F34]

AND SMD.[F35] = MD.[F35]

AND SMD.[F36] = MD.[F36]

AND SMD.[F37] = MD.[F37]

AND SMD.[F38] = MD.[F38]

AND SMD.[F39] = MD.[F39]

AND SMD.[F40] = MD.[F40]

AND SMD.[F41] = MD.[F41]

AND SMD.[F42] = MD.[F42]

AND SMD.[F43] = MD.[F43]

AND SMD.[F44] = MD.[F44]

AND SMD.[F45] = MD.[F45]

*/

FOR XML AUTO, TYPE) AS MXML

FROM [InDemand_Month] MD

)

,BASE_COLS AS

(

SELECT

BD.[F1] AS F1

,BD.[F2] AS F2

,BD.[F3] AS F3

,BD.[F4] AS F4

,BD.[F5] AS F5

,BD.[F6] AS F6

,BD.[F7] AS F7

,BD.[F8] AS F8

,BD.[F9] AS F9

,BD.[F10] AS F10

,BD.[F11] AS F11

,BD.[F12] AS F12

,BD.[F13] AS F13

,BD.[F14] AS F14

,BD.[F15] AS F15

,BD.[F16] AS F16

,BD.[F17] AS F17

,BD.[F18] AS F18

,BD.[F19] AS F19

,BD.[F20] AS F20

,BD.[F21] AS F21

,BD.[F22] AS F22

,BD.[F23] AS F23

,BD.[F24] AS F24

,BD.[F25] AS F25

,BD.[F26] AS F26

,BD.[F27] AS F27

,BD.[F28] AS F28

,BD.[F29] AS F29

,BD.[F30] AS F30

,BD.[F31] AS F31

,BD.[F32] AS F32

,BD.[F33] AS F33

,BD.[F34] AS F34

,BD.[F35] AS F35

,BD.[F36] AS F36

,BD.[F37] AS F37

,BD.[F38] AS F38

,BD.[F39] AS F39

,BD.[F40] AS F40

,BD.[F41] AS F41

,BD.[F42] AS F42

,BD.[F43] AS F43

,BD.[F44] AS F44

,BD.[F45] AS F45

/*******************************************************

* I think this is the part I don't understand enough. *

* It looks like because I tried to over simplify my *

* example, this solution is using XML to get the date *

* from the column name where as the real date is in *

* the 1st row of data. Hence when this is executed I *

* get an 'Error converting data type nvarchar to float*

* message. *

* I could be wrong about the source of the error *

* however. *

*******************************************************/

/* The "date" column names start with a digit which xml replaces with

the hexadecimal value of the digit. Since we are unlikely dealing

with anything greater than the 2000 range, we can just do a straight

replace

*/

,CONVERT(DATE,REPLACE(BASE.DATA.value('local-name(.)','VARCHAR(25)'),'_x0032_','2'),127) AS WKDAY

,BASE.DATA.value('.','FLOAT') AS WKVALUE

FROM BASE_DATA BD

/* The nodes function is used here with a wildcard to fetch all

attributes within the XML

*/

OUTER APPLY BD.MXML.nodes('//*/@*') AS BASE(DATA)

WHERE BASE.DATA.value('local-name(.)','VARCHAR(25)') NOT IN ('[Col Name 1]'

,'ID'

,'[F1]'

,'[F2]'

,'[F3]'

,'[F4]'

,'[F5]'

,'[F6]'

,'[F7]'

,'[F8]'

,'[F9]'

,'[F10]'

,'[F11]'

,'[F12]'

,'[F13]'

,'[F14]'

,'[F15]'

,'[F16]'

,'[F17]'

,'[F18]'

,'[F19]'

,'[F20]'

,'[F21]'

,'[F22]'

,'[F23]'

,'[F24]'

,'[F25]'

,'[F26]'

,'[F27]'

,'[F28]'

,'[F29]'

,'[F30]'

,'[F31]'

,'[F32]'

,'[F33]'

,'[F34]'

,'[F35]'

,'[F36]'

,'[F37]'

,'[F38]'

,'[F39]'

,'[F40]'

,'[F41]'

,'[F42]'

,'[F43]'

,'[F44]'

,'[F45]'

,'[F46]'

,'[F47]'

,'[F48]'

,'[F49]'

,'[F50]'

,'[F51]'

,'[F52]'

,'[F53]'

,'[F54]'

,'[F55]'

,'[F56]'

,'[F57]'

,'[F58]'

,'[F59]'

,'[F60]'

,'[F61]'

,'[F62]'

,'[F63]'

,'[F64]'

,'[F65]'

,'[F66]'

,'[F67]'

,'[F68]'

,'[F69]'

,'[F70]'

,'[F71]'

,'[F72]'

,'[F73]'

,'[F74]'

,'[F75]'

,'[F76]'

,'[F77]'

,'[F78]'

,'[F79]'

,'[F80]'

,'[F81]'

,'[F82]'

,'[F83]'

,'[F84]'

,'[F85]'

,'[F86]'

,'[F87]'

,'[F88]'

,'[F89]'

,'[F90]'

,'[F91]'

,'[F92]'

,'[F93]'

,'[F94]'

,'[F95]'

,'[F96]'

,'[F97]'

,'[F98]'

,'[F99]'

,'[F100]'

)

--'YearMonth','RequestID','Name')

)

,COLUMNAR_DATA AS

(

SELECT

BC.[F1] AS [F1]

,BC.[F2] AS [F2]

,BC.[F3] AS [F3]

,BC.[F4] AS F4

,BC.[F5] AS F5

,BC.[F6] AS F6

,BC.[F7] AS F7

,BC.[F8] AS F8

,BC.[F9] AS F9

,BC.[F10] AS F10

,BC.[F11] AS F11

,BC.[F12] AS F12

,BC.[F13] AS F13

,BC.[F14] AS F14

,BC.[F15] AS F15

,BC.[F16] AS F16

,BC.[F17] AS F17

,BC.[F18] AS F18

,BC.[F19] AS F19

,BC.[F20] AS F20

,BC.[F21] AS F21

,BC.[F22] AS F22

,BC.[F23] AS F23

,BC.[F24] AS F24

,BC.[F25] AS F25

,BC.[F26] AS F26

,BC.[F27] AS F27

,BC.[F28] AS F28

,BC.[F29] AS F29

,BC.[F30] AS F30

,BC.[F31] AS F31

,BC.[F32] AS F32

,BC.[F33] AS F33

,BC.[F34] AS F34

,BC.[F35] AS F35

,BC.[F36] AS F36

,BC.[F37] AS F37

,BC.[F38] AS F38

,BC.[F39] AS F39

,BC.[F40] AS F40

,BC.[F41] AS F41

,BC.[F42] AS F42

,BC.[F43] AS F43

,BC.[F44] AS F44

,BC.[F45] AS F45

,(YEAR(BC.WKDAY) * 100) + MONTH(BC.WKDAY) AS WMISO

,BC.WKDAY

,BC.WKVALUE

FROM BASE_COLS BC

)

--Select * from SIMPLE_CAL

--Select * from COLUMNAR_DATA

--Select * from BASE_COLS

/* Header section */

SELECT

'[Col Name 1]' as [F1]

,'[Col Name 2]' as [F2]

,'[Col Name 3]' as [F3]

,'[Col Name 4]' as [F4]

,'[Col Name 5]' as [F5]

,'[Col Name 6]' as [F6]

,'[Col Name 7]' as [F7]

,'[Col Name 8]' as [F8]

,'[Col Name 9]' as [F9]

,'[Col Name 10]' as [F10]

,'[Col Name 11]' as [F11]

,'[Col Name 12]' as [F12]

,'[Col Name 13]' as [F13]

,'[Col Name 14]' as [F14]

,'[Col Name 15]' as [F15]

,'[Col Name 16]' as [F16]

,'[Col Name 17]' as [F17]

,'[Col Name 18]' as [F18]

,'[Col Name 19]' as [F19]

,'[Col Name 20]' as [F20]

,'[Col Name 21]' as [F21]

,'[Col Name 22]' as [F22]

,'[Col Name 23]' as [F23]

,'[Col Name 24]' as [F24]

,'[Col Name 25]' as [F25]

,'[Col Name 26]' as [F26]

,'[Col Name 27]' as [F27]

,'[Col Name 28]' as [F28]

,'[Col Name 29]' as [F29]

,'[Col Name 30]' as [F30]

,'[Col Name 31]' as [F31]

,'[Col Name 32]' as [F32]

,'[Col Name 33]' as [F33]

,'[Col Name 34]' as [F34]

,'[Col Name 35]' as [F35]

,'[Col Name 36]' as [F36]

,'[Col Name 37]' as [F37]

,'[Col Name 38]' as [F38]

,'[Col Name 39]' as [F39]

,'[Col Name 40]' as [F40]

,'[Col Name 41]' as [F41]

,'[Col Name 42]' as [F42]

,'[Col Name 43]' as [F43]

,'[Col Name 44]' as [F44]

,'[Col Name 45]' as [F45]

,MAX(CASE WHEN SC.N = 0 THEN QUOTENAME(SC.WFD) END) AS F46

,MAX(CASE WHEN SC.N = 1 THEN QUOTENAME(SC.WFD) END) AS F47

,MAX(CASE WHEN SC.N = 2 THEN QUOTENAME(SC.WFD) END) AS F48

,MAX(CASE WHEN SC.N = 3 THEN QUOTENAME(SC.WFD) END) AS F49

,MAX(CASE WHEN SC.N = 4 THEN QUOTENAME(SC.WFD) END) AS F50

,MAX(CASE WHEN SC.N = 5 THEN QUOTENAME(SC.WFD) END) AS F51

,MAX(CASE WHEN SC.N = 6 THEN QUOTENAME(SC.WFD) END) AS F52

,MAX(CASE WHEN SC.N = 7 THEN QUOTENAME(SC.WFD) END) AS F53

,MAX(CASE WHEN SC.N = 8 THEN QUOTENAME(SC.WFD) END) AS F54

,MAX(CASE WHEN SC.N = 9 THEN QUOTENAME(SC.WFD) END) AS F55

,MAX(CASE WHEN SC.N = 10 THEN QUOTENAME(SC.WFD) END) AS F56

,MAX(CASE WHEN SC.N = 11 THEN QUOTENAME(SC.WFD) END) AS F57

,MAX(CASE WHEN SC.N = 12 THEN QUOTENAME(SC.WFD) END) AS F58

,MAX(CASE WHEN SC.N = 13 THEN QUOTENAME(SC.WFD) END) AS F59

,MAX(CASE WHEN SC.N = 14 THEN QUOTENAME(SC.WFD) END) AS F60

,MAX(CASE WHEN SC.N = 15 THEN QUOTENAME(SC.WFD) END) AS F61

,MAX(CASE WHEN SC.N = 16 THEN QUOTENAME(SC.WFD) END) AS F62

,MAX(CASE WHEN SC.N = 17 THEN QUOTENAME(SC.WFD) END) AS F63

,MAX(CASE WHEN SC.N = 18 THEN QUOTENAME(SC.WFD) END) AS F64

,MAX(CASE WHEN SC.N = 19 THEN QUOTENAME(SC.WFD) END) AS F65

,MAX(CASE WHEN SC.N = 20 THEN QUOTENAME(SC.WFD) END) AS F66

,MAX(CASE WHEN SC.N = 21 THEN QUOTENAME(SC.WFD) END) AS F67

,MAX(CASE WHEN SC.N = 22 THEN QUOTENAME(SC.WFD) END) AS F68

,MAX(CASE WHEN SC.N = 23 THEN QUOTENAME(SC.WFD) END) AS F69

,MAX(CASE WHEN SC.N = 24 THEN QUOTENAME(SC.WFD) END) AS F70

,MAX(CASE WHEN SC.N = 25 THEN QUOTENAME(SC.WFD) END) AS F71

,MAX(CASE WHEN SC.N = 26 THEN QUOTENAME(SC.WFD) END) AS F72

,MAX(CASE WHEN SC.N = 27 THEN QUOTENAME(SC.WFD) END) AS F73

,MAX(CASE WHEN SC.N = 28 THEN QUOTENAME(SC.WFD) END) AS F74

,MAX(CASE WHEN SC.N = 29 THEN QUOTENAME(SC.WFD) END) AS F75

,MAX(CASE WHEN SC.N = 30 THEN QUOTENAME(SC.WFD) END) AS F76

,MAX(CASE WHEN SC.N = 31 THEN QUOTENAME(SC.WFD) END) AS F77

,MAX(CASE WHEN SC.N = 32 THEN QUOTENAME(SC.WFD) END) AS F78

,MAX(CASE WHEN SC.N = 33 THEN QUOTENAME(SC.WFD) END) AS F79

,MAX(CASE WHEN SC.N = 34 THEN QUOTENAME(SC.WFD) END) AS F80

,MAX(CASE WHEN SC.N = 35 THEN QUOTENAME(SC.WFD) END) AS F81

,MAX(CASE WHEN SC.N = 36 THEN QUOTENAME(SC.WFD) END) AS F82

,MAX(CASE WHEN SC.N = 37 THEN QUOTENAME(SC.WFD) END) AS F83

,MAX(CASE WHEN SC.N = 38 THEN QUOTENAME(SC.WFD) END) AS F84

,MAX(CASE WHEN SC.N = 39 THEN QUOTENAME(SC.WFD) END) AS F85

,MAX(CASE WHEN SC.N = 40 THEN QUOTENAME(SC.WFD) END) AS F86

,MAX(CASE WHEN SC.N = 41 THEN QUOTENAME(SC.WFD) END) AS F87

,MAX(CASE WHEN SC.N = 42 THEN QUOTENAME(SC.WFD) END) AS F88

,MAX(CASE WHEN SC.N = 43 THEN QUOTENAME(SC.WFD) END) AS F89

,MAX(CASE WHEN SC.N = 44 THEN QUOTENAME(SC.WFD) END) AS F90

,MAX(CASE WHEN SC.N = 45 THEN QUOTENAME(SC.WFD) END) AS F91

,MAX(CASE WHEN SC.N = 46 THEN QUOTENAME(SC.WFD) END) AS F92

,MAX(CASE WHEN SC.N = 47 THEN QUOTENAME(SC.WFD) END) AS F93

,MAX(CASE WHEN SC.N = 48 THEN QUOTENAME(SC.WFD) END) AS F94

,MAX(CASE WHEN SC.N = 49 THEN QUOTENAME(SC.WFD) END) AS F95

,MAX(CASE WHEN SC.N = 50 THEN QUOTENAME(SC.WFD) END) AS F96

,MAX(CASE WHEN SC.N = 51 THEN QUOTENAME(SC.WFD) END) AS F97

,MAX(CASE WHEN SC.N = 52 THEN QUOTENAME(SC.WFD) END) AS F98

,MAX(CASE WHEN SC.N = 53 THEN QUOTENAME(SC.WFD) END) AS F99

,MAX(CASE WHEN SC.N = 54 THEN QUOTENAME(SC.WFD) END) AS F100

FROM SIMPLE_CAL SC

UNION ALL

/* Data section

Simple cross-tab

*/

SELECT

CD.[F1]

,CD.[F2]

,CD.[F3]

,CD.[F4]

,CD.[F5]

,CD.[F6]

,CD.[F7]

,CD.[F8]

,CD.[F9]

,CD.[F10]

,CD.[F11]

,CD.[F12]

,CD.[F13]

,CD.[F14]

,CD.[F15]

,CD.[F16]

,CD.[F17]

,CD.[F18]

,CD.[F19]

,CD.[F20]

,CD.[F21]

,CD.[F22]

,CD.[F23]

,CD.[F24]

,CD.[F25]

,CD.[F26]

,CD.[F27]

,CD.[F28]

,CD.[F29]

,CD.[F30]

,CD.[F31]

,CD.[F32]

,CD.[F33]

,CD.[F34]

,CD.[F35]

,CD.[F36]

,CD.[F37]

,CD.[F38]

,CD.[F39]

,CD.[F40]

,CD.[F41]

,CD.[F42]

,CD.[F43]

,CD.[F44]

,CD.[F45]

/*

,MAX(CASE WHEN SC.N = 0 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F46

,MAX(CASE WHEN SC.N = 1 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F47

,MAX(CASE WHEN SC.N = 2 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F48

,MAX(CASE WHEN SC.N = 3 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F49

,MAX(CASE WHEN SC.N = 4 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F50

,MAX(CASE WHEN SC.N = 5 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F51

,MAX(CASE WHEN SC.N = 6 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F52

,MAX(CASE WHEN SC.N = 7 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F53

,MAX(CASE WHEN SC.N = 8 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F54

,MAX(CASE WHEN SC.N = 9 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F55

,MAX(CASE WHEN SC.N = 10 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F56

,MAX(CASE WHEN SC.N = 11 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F57

,MAX(CASE WHEN SC.N = 12 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F58

,MAX(CASE WHEN SC.N = 13 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F59

,MAX(CASE WHEN SC.N = 14 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F60

,MAX(CASE WHEN SC.N = 15 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F61

,MAX(CASE WHEN SC.N = 16 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F62

,MAX(CASE WHEN SC.N = 17 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F63

,MAX(CASE WHEN SC.N = 18 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F64

,MAX(CASE WHEN SC.N = 19 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F65

,MAX(CASE WHEN SC.N = 20 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F66

,MAX(CASE WHEN SC.N = 21 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F67

,MAX(CASE WHEN SC.N = 22 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F68

,MAX(CASE WHEN SC.N = 23 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F69

,MAX(CASE WHEN SC.N = 24 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F70

,MAX(CASE WHEN SC.N = 25 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F71

,MAX(CASE WHEN SC.N = 26 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F72

,MAX(CASE WHEN SC.N = 27 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F73

,MAX(CASE WHEN SC.N = 28 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F74

,MAX(CASE WHEN SC.N = 29 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F75

,MAX(CASE WHEN SC.N = 30 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F76

,MAX(CASE WHEN SC.N = 31 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F77

,MAX(CASE WHEN SC.N = 32 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F78

,MAX(CASE WHEN SC.N = 33 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F79

,MAX(CASE WHEN SC.N = 34 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F80

,MAX(CASE WHEN SC.N = 35 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F81

,MAX(CASE WHEN SC.N = 36 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F82

,MAX(CASE WHEN SC.N = 37 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F83

,MAX(CASE WHEN SC.N = 38 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F84

,MAX(CASE WHEN SC.N = 39 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F85

,MAX(CASE WHEN SC.N = 40 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F86

,MAX(CASE WHEN SC.N = 41 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F87

,MAX(CASE WHEN SC.N = 42 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F88

,MAX(CASE WHEN SC.N = 43 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F89

,MAX(CASE WHEN SC.N = 44 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F90

,MAX(CASE WHEN SC.N = 45 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F91

,MAX(CASE WHEN SC.N = 46 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F92

,MAX(CASE WHEN SC.N = 47 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F93

,MAX(CASE WHEN SC.N = 48 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F94

,MAX(CASE WHEN SC.N = 49 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F95

,MAX(CASE WHEN SC.N = 50 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F96

,MAX(CASE WHEN SC.N = 51 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F97

,MAX(CASE WHEN SC.N = 52 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F98

,MAX(CASE WHEN SC.N = 53 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F99

,MAX(CASE WHEN SC.N = 54 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,'0.0'),0) END) AS F100

*/

,MAX(CASE WHEN SC.N = 0 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F46

,MAX(CASE WHEN SC.N = 1 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F47

,MAX(CASE WHEN SC.N = 2 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F48

,MAX(CASE WHEN SC.N = 3 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F49

,MAX(CASE WHEN SC.N = 4 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F50

,MAX(CASE WHEN SC.N = 5 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F51

,MAX(CASE WHEN SC.N = 6 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F52

,MAX(CASE WHEN SC.N = 7 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F53

,MAX(CASE WHEN SC.N = 8 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F54

,MAX(CASE WHEN SC.N = 9 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F55

,MAX(CASE WHEN SC.N = 10 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F56

,MAX(CASE WHEN SC.N = 11 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F57

,MAX(CASE WHEN SC.N = 12 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F58

,MAX(CASE WHEN SC.N = 13 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F59

,MAX(CASE WHEN SC.N = 14 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F60

,MAX(CASE WHEN SC.N = 15 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F61

,MAX(CASE WHEN SC.N = 16 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F62

,MAX(CASE WHEN SC.N = 17 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F63

,MAX(CASE WHEN SC.N = 18 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F64

,MAX(CASE WHEN SC.N = 19 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F65

,MAX(CASE WHEN SC.N = 20 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F66

,MAX(CASE WHEN SC.N = 21 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F67

,MAX(CASE WHEN SC.N = 22 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F68

,MAX(CASE WHEN SC.N = 23 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F69

,MAX(CASE WHEN SC.N = 24 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F70

,MAX(CASE WHEN SC.N = 25 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F71

,MAX(CASE WHEN SC.N = 26 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F72

,MAX(CASE WHEN SC.N = 27 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F73

,MAX(CASE WHEN SC.N = 28 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F74

,MAX(CASE WHEN SC.N = 29 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F75

,MAX(CASE WHEN SC.N = 30 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F76

,MAX(CASE WHEN SC.N = 31 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F77

,MAX(CASE WHEN SC.N = 32 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F78

,MAX(CASE WHEN SC.N = 33 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F79

,MAX(CASE WHEN SC.N = 34 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F80

,MAX(CASE WHEN SC.N = 35 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F81

,MAX(CASE WHEN SC.N = 36 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F82

,MAX(CASE WHEN SC.N = 37 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F83

,MAX(CASE WHEN SC.N = 38 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F84

,MAX(CASE WHEN SC.N = 39 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F85

,MAX(CASE WHEN SC.N = 40 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F86

,MAX(CASE WHEN SC.N = 41 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F87

,MAX(CASE WHEN SC.N = 42 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F88

,MAX(CASE WHEN SC.N = 43 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F89

,MAX(CASE WHEN SC.N = 44 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F90

,MAX(CASE WHEN SC.N = 45 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F91

,MAX(CASE WHEN SC.N = 46 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F92

,MAX(CASE WHEN SC.N = 47 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F93

,MAX(CASE WHEN SC.N = 48 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F94

,MAX(CASE WHEN SC.N = 49 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F95

,MAX(CASE WHEN SC.N = 50 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F96

,MAX(CASE WHEN SC.N = 51 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F97

,MAX(CASE WHEN SC.N = 52 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F98

,MAX(CASE WHEN SC.N = 53 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F99

,MAX(CASE WHEN SC.N = 54 THEN CONVERT(NVARCHAR(20),Coalesce(CD.WKVALUE,0),0) END) AS F100

FROM SIMPLE_CAL SC

LEFT JOIN COLUMNAR_DATA CD

ON CD.WMISO = SC.WMISO

WHERE CD.[F1] <> '[Col Name 1]' -- IS NOT NULL

GROUP BY CD.[F1]

,CD.[F2]

,CD.[F3]

,CD.[F4]

,CD.[F5]

,CD.[F6]

,CD.[F7]

,CD.[F8]

,CD.[F9]

,CD.[F10]

,CD.[F11]

,CD.[F12]

,CD.[F13]

,CD.[F14]

,CD.[F15]

,CD.[F16]

,CD.[F17]

,CD.[F18]

,CD.[F19]

,CD.[F20]

,CD.[F21]

,CD.[F22]

,CD.[F23]

,CD.[F24]

,CD.[F25]

,CD.[F26]

,CD.[F27]

,CD.[F28]

,CD.[F29]

,CD.[F30]

,CD.[F31]

,CD.[F32]

,CD.[F33]

,CD.[F34]

,CD.[F35]

,CD.[F36]

,CD.[F37]

,CD.[F38]

,CD.[F39]

,CD.[F40]

,CD.[F41]

,CD.[F42]

,CD.[F43]

,CD.[F44]

,CD.[F45]

;

Jeff Moden SSC Guru Points: 1004600 More actions · Answer 7

Still, enquiring minds want to know, what is the "YearMonth" column for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jesse.owen Old Hand Points: 310 More actions · Answer 8

Hi Jeff,

That was just my bad putting it as a column in the original sample data. I was trying to create a simplified version of the input table but failed. I think if I need help on other issues I'll just accept that most people on here know more then me and give the detailed version of the problem so I don't create confusion..

The actual input table is now in the new code above. The column names are just F1 to F100.

The labels that are in the 1st row are the names of the columns in the Excel workbook. I have just called them Col Name 1 etc. In RL they are things like Project Name, Role, Project Status etc.

I've removed the YearMonth and all other column names from the above example as they do not contain any data relevant to the problem. There actually isn't a YearMonth column in either the input or output of the issue I'm having. It is added to tables further down the processing chain.

The YearMonth column in the database is actually an output of the processing that is done to the result of the query I am trying to write now (with the help of this community of course ). It currently holds the date of the relevant Monday for that week. (It used to hold the month date but the whole database was converted from using months to weeks about 2 years ago and this field was never updated (Budgets, timeframes, new high priorities etc.) The source system for the data outputs 2 files each day, one month based and one week based. The current input mechanism is based on the week file now. Unfortunately it only goes out to +24 weeks. A number of our managers wants the data out to over a year. The system owners (in their great wisdom) have said no to changing the weekly file from the source system to have more data. So the option I am trying is to import the weekly file and then append the monthly file after splitting it into weeks (with the query above). Doing it this way means that all of the post upload processing scripts and views and reports will still work with minimal changes which is important.

Sorry for the confusion. If you look at the new code example, hopefully that makes it clearer.

Thanks,

Jess.

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 9

The initial solution does convert column names to dates, something that's not applicable in the later data sample, no problem really, will post the code when I have the time. In the meantime, the local-name(.) XQuery function returns the name of the attribute/column, that's what needs to change.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 10

I'd probably generate dynamic SQL in this case, as it's much clearer to me. Sample below. I'm sure it will need tweaked but it should give you enough of a start to test it out if you want to.

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL

DROP TABLE #sql

CREATE TABLE #sql (

ident int IDENTITY(1, 1) NOT NULL,

sort_sequence tinyint NULL,

sort_value varchar(30) NULL,

sql_text varchar(500) NOT NULL

)

DECLARE @min_date date --min(first) valid date in the spreadsheet

DECLARE @min_column_number tinyint --relative column# associated with the min date

DECLARE @max_date date --max(last) valid date in the spreadsheet

DECLARE @max_column_number tinyint --relative column# associated with the max date

DECLARE @sql varchar(max)

------------------------------------------------------------------------------------------------------------------------

--find the first and last valid month labels in the first row of the spreadsheet (ID = 1)

SELECT

@min_date = LEFT(min_month_and_month_number, 10),

@min_column_number = SUBSTRING(min_month_and_month_number, 11, 3),

@max_date = LEFT(max_month_and_month_number, 10),

@max_column_number = SUBSTRING(max_month_and_month_number, 11, 3)

FROM (

SELECT

MIN(month_date + RIGHT('00' + CAST(month_number AS varchar(3)), 3)) AS min_month_and_month_number,

MAX(month_date + RIGHT('00' + CAST(month_number AS varchar(3)), 3)) AS max_month_and_month_number

FROM (

SELECT TOP (1) *

FROM dbo.InDemand_Month

WHERE

ID = 1

) AS months_row

CROSS APPLY (

VALUES

( 1, F1), ( 2, F2), ( 3, F3), ( 4, F4), ( 5, F5), ( 6, F6), ( 7, F7), ( 8, F8), ( 9, F9),(10,F10),

(11,F11), (12,F12), (13,F13), (14,F14), (15,F15), (16,F16), (17,F17), (18,F18), (19,F19),(20,F20),

(21,F21), (22,F22), (23,F23), (24,F24), (25,F25), (26,F26), (27,F27), (28,F28), (29,F29),(30,F20),

(31,F31), (32,F32), (33,F33), (34,F34), (35,F35), (36,F36), (37,F37), (38,F38), (39,F39),(40,F20),

(41,F41), (42,F42), (43,F43), (44,F44), (45,F45), (46,F46), (47,F47), (48,F48), (49,F49),(50,F20),

(51,F51), (52,F52), (53,F53), (54,F54), (55,F55), (56,F56), (57,F57), (58,F58), (59,F59),(60,F20),

(61,F61), (62,F62), (63,F63), (64,F64), (65,F65), (66,F66), (67,F67), (68,F68), (69,F69),(70,F20),

(71,F71), (72,F72), (73,F73), (74,F74), (75,F75), (76,F76), (77,F77), (78,F78), (79,F79),(80,F20),

(81,F81), (82,F82), (83,F83), (84,F84), (85,F85), (86,F86), (87,F87), (88,F88), (89,F89),(90,F20),

(91,F81), (92,F82), (93,F93), (94,F94), (95,F95), (96,F96), (97,F97), (98,F98), (99,F99),(100,F10)

) AS all_months(month_number, month_date)

WHERE

month_date LIKE '[2][0-5][0-9][0-9][-]%'

) AS min_max_months;

--force min date to a Monday

SELECT @min_date = DATEADD(DAY, -DATEDIFF(DAY, 0, month_day_7) % 7, month_day_7)

FROM (

SELECT DATEADD(DAY, 6, DATEADD(MONTH, DATEDIFF(MONTH, 0, @min_date), 0)) AS month_day_7

) AS assign_alias_names1

SELECT @min_date AS min_month, @min_column_number AS min_column_number, @max_date AS max_month, @max_column_number AS max_column_number

------------------------------------------------------------------------------------------------------------------------

--load sql text into a temp table from which it can be concat into the final statement to run;

--use ~~ in place of char(10) (lf) because xml converts it to &... garbage

INSERT INTO #sql ( sort_sequence, sort_value, sql_text )

SELECT 10, '0', 'SELECT im.ID'

;WITH

cteTally10 AS (

SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

),

cteTally1000 AS (

SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally

FROM cteTally10 c1

CROSS JOIN cteTally10 c2

CROSS JOIN cteTally10 c3

)

INSERT INTO #sql ( sort_sequence, sort_value, sql_text )

SELECT 100 AS sort_sequence, RIGHT('00' + CAST(columns.tally AS varchar(10)), 3) AS sort_value,

'~~,MAX(im.F' +

CAST(columns.tally AS varchar(3)) + ') ' +

'AS [F' + + CAST(columns.tally AS varchar(3)) + ']' AS sql_text

FROM cteTally1000 columns

WHERE

columns.tally > 0 AND

columns.tally < @min_column_number

UNION ALL

SELECT 200 AS sort_sequence, 'z' + CONVERT(varchar(10), DATEADD(DAY, weeks.tally * 7, @min_date), 120) AS sort_value,

'~~,MAX(im.F' +

CAST(@min_column_number + DATEDIFF(month, @min_date, DATEADD(DAY, weeks.tally * 7, @min_date)) AS varchar(3)) + ') ' +

'AS [' + CONVERT(varchar(10), DATEADD(DAY, weeks.tally * 7, @min_date), 120) + ']'

FROM cteTally1000 weeks

WHERE

DATEADD(DAY, weeks.tally * 7, @min_date) < DATEADD(MONTH, 1, @max_date)

ORDER BY sort_sequence, sort_value

INSERT INTO #sql ( sort_sequence, sort_value, sql_text )

SELECT 201, '0', '~~FROM dbo.InDemand_Month im~~WHERE $where$~~GROUP BY im.ID~~ORDER BY im.ID'

--SELECT * FROM #sql ORDER BY ident

------------------------------------------------------------------------------------------------------------------------

--concat the sql, and replace any "codes" used to avoid xml issues with the appropriate text

SELECT @sql = REPLACE(REPLACE(CAST((

SELECT sql_text + ''

FROM #sql

ORDER BY ident

FOR XML PATH('')) AS varchar(max)), '~~', CHAR(10)), '$where$', 'im.ID >= 2')

SELECT @sql AS final_sql_to_run

--SELECT * FROM dbo.InDemand_Month ORDER BY ID

EXEC(@sql)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jesse.owen Old Hand Points: 310 More actions · Answer 11

Thanks Scott.

Your solution works and the output data from the row with ID = 2 onwards is great. Has all of the weeks out to the end of the date range which is required

The only issue is that I need the 1st row (ID = 1) as this ties in with the established code I need to integrate with. I have tried to look at how I can do this in your solution but unfortunately once you guys start with the XML I get lost.

I can see that this part is where you set the column names for the date columns. I can change it to the '[FXX]' name but would need to add an index to change per column. Not sure how to achieve that.

SELECT 200 AS sort_sequence, 'z' + CONVERT(varchar(10), DATEADD(DAY, weeks.tally * 7, @min_date), 120) AS sort_value,

'~~,MAX(im.F' +

CAST(@min_column_number + DATEDIFF(month, @min_date, DATEADD(DAY, weeks.tally * 7, @min_date)) AS varchar(3)) + ') ' +

'AS [F' + CAST(@min_column_number + DATEDIFF(month, @min_date, DATEADD(DAY, weeks.tally * 7, @min_date)) AS varchar(3)) + ']'

-- 'AS [' + CONVERT(varchar(10), DATEADD(DAY, weeks.tally * 7, @min_date), 120) + ']'

The other part I couldn't workout is how to add the 1st row which contains the column labels (i.e. "Col Name 1", "Col Name 2 etc. + the week dates which you currently have as the column names.

E.g. Column Row 1 Data

F41 [Col Name 41]

F42 [Col Name 42]

F43 [Col Name 43]

F44 [Col Name 44]

F45 [Col Name 45]

F46 [2014-08-01]

F47 [2014-08-08]

F48 [2014-08-15]

F49 [2014-08-22]

F50 [2014-08-29]

Maybe I could combine the 2 solutions but that would seem messy.

Cheers,

Jess.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 12

------------------------------------------------------------------------------------------------------------------------

--declare temp tables and variables

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL

DROP TABLE #sql

CREATE TABLE #sql (

ident int IDENTITY(1, 1) NOT NULL,

sort_sequence smallint NULL,

sort_value varchar(30) NULL,

sql_text varchar(500) NOT NULL

)

DECLARE @min_date date --min(first) valid date in the spreadsheet

DECLARE @min_column_number smallint --relative column# associated with the min date

DECLARE @max_date date --max(last) valid date in the spreadsheet

DECLARE @max_column_number smallint --relative column# associated with the max date

DECLARE @sql_text varchar(max)