Switch rows and columns

  • 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?

  • 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)

  • 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.

  • 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![/I]

    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