Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Switch rows and columns Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 1:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:00 AM
Points: 827, Visits: 1,693
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-01	2011-08-01	2011-09-01	2011-10-01	2011-11-01	2011-12-01	2012-01-01	2012-02-01	2012-03-01	2012-04-01	2012-05-01	2012-06-01	2012-07-01
val1 2 3 4 5 6 7 8 9 1 1 1 1 1
val2 2 3 4 1 5 6 1 1 1 7 8 9 1
val3 3 2 1 1 4 1 1 3 1 1 2 1 4
val4 1 4 1 1 3 1 1 2 1 1 1 4 1

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?
Post #1356204
Posted Friday, September 7, 2012 3:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 4:34 AM
Points: 1,234, Visits: 1,434
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)




Post #1356266
Posted Monday, September 10, 2012 6:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:00 AM
Points: 827, Visits: 1,693
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.
Post #1356682
Posted Tuesday, September 11, 2012 7:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1357758
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse