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)