Microsoft Access Cross-tab Query - Convert to MSSQL View

Question

I have the following table in access (odbc linked to actual mssql table) Table

I can create a cross-tab query of the table to transform it into this format

Here is the sql that is generated in access:

TRANSFORM First(dbo_85137_PHY_Long_MP.[StatColumnValue]) AS FirstOfStatColumnValue
SELECT dbo_85137_PHY_Long_MP.[StatDate]
FROM dbo_85137_PHY_Long_MP
GROUP BY dbo_85137_PHY_Long_MP.[StatDate]
PIVOT dbo_85137_PHY_Long_MP.[StatColumnName];

I have tried to create the same sql within mssql to no avail (trying to create a view)

What you want is commonly called a PIVOT query and is something MySQL doesn't natively support. This question might help you get started, though it will be an ugly query for you: http://stackoverflow.com/questions/7888631/how-to-display-rows-as-columns-in-mysql — Andrew, May 24 '12 at 15:33

score 5 · Accepted Answer · answered May 25 '12 at 12:15

Since you are using SQL Server you will want to use the PIVOT operator. You can do this two ways, either via a Static PIVOT or a Dynamic PIVOT.

A Static PIVOT, means that you will code for each column that you need to rotate. Since it appears you have a lot of columns I doubt you will want to do it this way. But the code would be similar to this:

create table t1
(
  statno int,
  statdate datetime,
  statcolumnname varchar(50),
  statcolumnvalue int
)

insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-1MA.MP00-1MA', 17)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-1MA.MP01-1MA', 18)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-1MA.MP02-1MA', 18)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-1MA.MP03-1MA', 18)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-1ME.MP10-1ME', 26)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-1ME.MP11-1ME', 2)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-1ME.MP12-1ME', 2)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-2MC.MP08-2MC', 2)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-2MC.MP09-2MC', 22)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-2MC.MP0A-2MC', 22)
insert into t1 values (1, '4/1/2012 12:15:00', 'MPB-2MC.MP0B-2MC', 22)

select *
from 
(
  select statdate, statcolumnname, statcolumnvalue
  from t1
) x
pivot
(
  min(statcolumnvalue)
  for statcolumnname in ([MPB-1MA.MP00-1MA], [MPB-1MA.MP01-1MA], [MPB-1MA.MP02-1MA])
) p

Here is a SQL Fiddle with a working example.

That will be very cumbersome if you have a lot of columns. So you could also use a Dynamic Pivot which will generate the list of columns to rotate when you run the query. Here is the code:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) 
            FROM t1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT statdate, ' + @cols + ' from 
            (
                select statdate, statcolumnname, statcolumnvalue
                from t1
           ) x
            pivot 
            (
                 min(statcolumnvalue)
                for statcolumnname in (' + @cols + ')
            ) p '


execute(@query)

Both of them will give you the same results.

You get some extremely intelligent people out there. Thanks for your answer. Super useful!!! — Thomas, May 28 '12 at 09:51
By the way, regarding your dynamic pivot example. Can I create it as a stored procure or something that I can re-use it? — Thomas, May 28 '12 at 10:05
Yes you can place it in a stored procedure so you can re-use it. — Taryn, May 28 '12 at 12:39

Microsoft Access Cross-tab Query - Convert to MSSQL View

1 Answers1