MS SQL Server Pivot as an alternate for MS Access Tranform

Usually from small application development to large application development for single user based applications, I find MS Access considerabily easy and powerful.

One good proven table design can be a good starting point for autogenerating good working forms, queries as well reports.

Apart from default auto reports I find cross tab reports of ms access as one of the powerful feature that I seem not able to do in MS SQL Server.

Recently after searching and searching I found one good example working as similar to MS Access transform statement.

Still I doubt the processing speed of the following stmt.:

Schema Code:

create table test
(
id int,
value varchar(10),
name varchar(10)
)

insert into test values
(23, ‘red’, ‘color’),
(23, ‘fast’, ‘speed’),
(23, ‘green’, ‘Go’),
(24, ‘red’, ‘color’),
(24, ‘fast’, ‘speed’),
(25, ‘green’, ‘Go’)

Query/View Code:

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

select @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(name)
from test
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”)

set @query
= ‘SELECT id,’ + @cols + ‘ from
(
SELECT id, value, name
FROM test
) x
pivot
(
min(value)
for name in (‘ + @cols + ‘)
) p ‘

execute(@query)

Leave a Reply

Your email address will not be published. Required fields are marked *