September 7, 2012 at 1:12 pm
Hey all, getting a bit of friday afternoon brain freeze ...
Say I have this data:
CREATE TABLE #Test
(
ID INT IDENTITY PRIMARY KEY,
Date DATETIME,
Val1 INT,
Val2 INT,
Val3 INT,
Val4 INT
)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2011-07-01', 2, 2, 3, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2011-08-01', 3, 3, 2, 4)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2011-09-01', 4, 4, 1, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2011-10-01', 5, 1, 1, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2011-11-01', 6, 5, 4, 3)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2011-12-01', 7, 6, 1, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2012-01-01', 8, 1, 1, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2012-02-01', 9, 1, 3, 2)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2012-03-01', 1, 1, 1, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2012-04-01', 1, 7, 1, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2012-05-01', 1, 8, 2, 1)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2012-06-01', 1, 9, 1, 4)
INSERT INTO #Test (Date, Val1, Val2, Val3, Val4)
VALUES ('2012-07-01', 1, 1, 4, 1)
And I want this output (sorry for the messy output but you should get the idea):
2011-07-012011-08-012011-09-012011-10-012011-11-012011-12-012012-01-012012-02-012012-03-012012-04-012012-05-012012-06-012012-07-01
val12345678911111
val22341561117891
val33211411311214
val41411311211141
What's the best way of writing a script to produce that output? In my particular case there is a fixed number of columns, so I know that I could just write X select statements UNIONed together, one for each column in the original table, but short of doing that, what's the next best approach?
September 7, 2012 at 3:30 pm
I'm not sure if this is the best way, given you have a fairly small fixed data set, but it's an excuse for a dynamic pivot.
First get all the column names you need like this. They're not your exact column names, but you get the idea
DECLARE @columns varchar(max);
SELECT @columns = COALESCE (
@columns + ',[' + cast(DATEPART(yyyy, Date) as varchar) + '_' + cast(DATEPART(mm, Date) as varchar) + ']',
'[' + cast(DATEPART(yyyy, Date) as varchar)+ '_' + cast(DATEPART(mm, Date) as varchar) + ']'
)
FROM #Test
GROUP BY
DATEPART(yyyy, Date),DATEPART(MM, Date)
ORDER BY DATEPART(yyyy, Date),DATEPART(MM, Date);
SELECT @columns;
Next get a dynamic pivot on just one VALx, in this case VAL1
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT *
FROM ( SELECT ''Val1'' AS VALUE,
cast(DATEPART(yyyy, Date) as varchar) + ''_'' + cast(DATEPART(mm, Date) as varchar) AS DateMonth,
Val1
FROM #Test ) P
PIVOT (
MAX(Val1) FOR DateMonth in (' + @columns + ')
) AS PVT';
PRINT @sql
That will give you the Val1 row only.
As you only have VAL1 to VAL4 - its probably easier to just UNION this with 3 copies of itself, replacing VAL1 with VAL2, VAL3, VAL4. But its possible to continue ... with help from a tally table ... Replace the above dynamic pivot with this one to do it for you.
DECLARE @sql VARCHAR(MAX);
SELECT @sql = COALESCE ( @sql + ' SELECT *
FROM ( SELECT ''Val' + CAST(N-1 AS VARCHAR) + ''' AS VALUE,
cast(DATEPART(yyyy, Date) as varchar) + ''_'' + cast(DATEPART(mm, Date) as varchar) AS DateMonth,
Val' + CAST(N-1 AS VARCHAR) + '
FROM #Test ) P
PIVOT (
MAX(Val' + CAST(N-1 AS VARCHAR) + ') FOR DateMonth in (' + @columns + ')
) AS PVT '
+ CASE N WHEN 5 THEN '' ELSE ' UNION ' END
, '' )
FROM dbo.Tally AS N
WHERE N < 6;
PRINT @sql
EXEC (@SQL)
September 10, 2012 at 6:09 am
Yeah - I was kinda hoping to avoid having to write a series of SELECT statements, either using pivots or cross-tabs, and UNIONing them together, but if that's the only option, then so be it.
September 11, 2012 at 7:26 pm
Let's assume that you're interested in a rolling 13 months and you are not tied to the column names you have specified. You can do this by a combination of the CROSS APPLY VALUES approach to UNPIVOT (http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/) and a crosstab, like this:
DECLARE @StartMonth DATETIME = '2011-07-01'
SELECT val
,M01=MAX(CASE WHEN FixDate = DATEADD(m, 0, @StartMonth) THEN value ELSE NULL END)
,M02=MAX(CASE WHEN FixDate = DATEADD(m, 1, @StartMonth) THEN value ELSE NULL END)
,M03=MAX(CASE WHEN FixDate = DATEADD(m, 2, @StartMonth) THEN value ELSE NULL END)
,M04=MAX(CASE WHEN FixDate = DATEADD(m, 3, @StartMonth) THEN value ELSE NULL END)
,M05=MAX(CASE WHEN FixDate = DATEADD(m, 4, @StartMonth) THEN value ELSE NULL END)
,M06=MAX(CASE WHEN FixDate = DATEADD(m, 5, @StartMonth) THEN value ELSE NULL END)
,M07=MAX(CASE WHEN FixDate = DATEADD(m, 6, @StartMonth) THEN value ELSE NULL END)
,M08=MAX(CASE WHEN FixDate = DATEADD(m, 7, @StartMonth) THEN value ELSE NULL END)
,M09=MAX(CASE WHEN FixDate = DATEADD(m, 8, @StartMonth) THEN value ELSE NULL END)
,M10=MAX(CASE WHEN FixDate = DATEADD(m, 9, @StartMonth) THEN value ELSE NULL END)
,M11=MAX(CASE WHEN FixDate = DATEADD(m, 10, @StartMonth) THEN value ELSE NULL END)
,M12=MAX(CASE WHEN FixDate = DATEADD(m, 11, @StartMonth) THEN value ELSE NULL END)
,M13=MAX(CASE WHEN FixDate = DATEADD(m, 12, @StartMonth) THEN value ELSE NULL END)
FROM #Test
CROSS APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, Date), 0)) a (FixDate)
CROSS APPLY (
VALUES ('val1', val1)
,('val2', val2)
,('val3', val3)
,('val4', val4)) b (val, value)
GROUP BY val
The CROSS APPLY to create "FixDate" may not be necessary if your data is already truncated to the first of the month.
If you must have the column names as you've specified, you can dump the above into another temporary table:
DECLARE @StartMonth DATETIME = '2011-07-01'
,@SQL VARCHAR(MAX)
CREATE TABLE #R13MO
(
Val VARCHAR(10),
M01 INT,
M02 INT,
M03 INT,
M04 INT,
M05 INT,
M06 INT,
M07 INT,
M08 INT,
M09 INT,
M10 INT,
M11 INT,
M12 INT,
M13 INT
)
INSERT INTO #R13MO
SELECT val
,M01=MAX(CASE WHEN FixDate = DATEADD(m, 0, @StartMonth) THEN value ELSE NULL END)
,M02=MAX(CASE WHEN FixDate = DATEADD(m, 1, @StartMonth) THEN value ELSE NULL END)
,M03=MAX(CASE WHEN FixDate = DATEADD(m, 2, @StartMonth) THEN value ELSE NULL END)
,M04=MAX(CASE WHEN FixDate = DATEADD(m, 3, @StartMonth) THEN value ELSE NULL END)
,M05=MAX(CASE WHEN FixDate = DATEADD(m, 4, @StartMonth) THEN value ELSE NULL END)
,M06=MAX(CASE WHEN FixDate = DATEADD(m, 5, @StartMonth) THEN value ELSE NULL END)
,M07=MAX(CASE WHEN FixDate = DATEADD(m, 6, @StartMonth) THEN value ELSE NULL END)
,M08=MAX(CASE WHEN FixDate = DATEADD(m, 7, @StartMonth) THEN value ELSE NULL END)
,M09=MAX(CASE WHEN FixDate = DATEADD(m, 8, @StartMonth) THEN value ELSE NULL END)
,M10=MAX(CASE WHEN FixDate = DATEADD(m, 9, @StartMonth) THEN value ELSE NULL END)
,M11=MAX(CASE WHEN FixDate = DATEADD(m, 10, @StartMonth) THEN value ELSE NULL END)
,M12=MAX(CASE WHEN FixDate = DATEADD(m, 11, @StartMonth) THEN value ELSE NULL END)
,M13=MAX(CASE WHEN FixDate = DATEADD(m, 12, @StartMonth) THEN value ELSE NULL END)
FROM #Test
CROSS APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, Date), 0)) a (FixDate)
CROSS APPLY (
VALUES ('val1', val1)
,('val2', val2)
,('val3', val3)
,('val4', val4)) b (val, value)
GROUP BY val
And then do a (relatively) simple dynamic SQL like this:
;WITH Tally (n) AS (
SELECT TOP 13 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns)
SELECT @sql = 'SELECT val' +
(
SELECT ',[' + REPLACE(CONVERT(VARCHAR(10), Date, 101), '/', '-') + ']=M' +
RIGHT('00' + CAST(MONTH(Date) AS VARCHAR), 2)
FROM (SELECT DATEADD(m, n, @StartMonth) FROM Tally) a(Date)
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') + ' FROM #R13MO'
PRINT @sql
EXEC (@SQL)
Or, you could also construct and execute the dynamic SQL to create #R13MO temp table and then execute a simple static DML query from that table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply